我有兩個表：users帶有id,name列的events表和帶有id,content和userId列的表。
我正在嘗試查詢一個表，該表從這兩個表回傳連接資訊，其中name和events串列示與用戶對應events的欄位陣列。content
這是我正在運行的查詢：
select
name, group_concat(content) as events
from
users
left join
events on id = userId
group by
userId
order by
id
但是，null除了只有一行之外，不會回傳具有值的行。我究竟做錯了什么？
用戶表
[
{
"id": 1,
"name": "Hugo Powlowski"
},
{
"id": 2,
"name": "Jeremy Littel II"
},
{
"id": 3,
"name": "Eleanor King"
},
{
"id": 4,
"name": "Rogelio Jacobson"
},
{
"id": 5,
"name": "Jerald Rowe PhD"
},
{
"id": 6,
"name": "Robyn Tromp"
},
{
"id": 7,
"name": "Norman Zboncak"
},
{
"id": 8,
"name": "Mr. Kristy Orn"
},
{
"id": 9,
"name": "Mrs. Olivia Trantow"
},
{
"id": 10,
"name": "Daniel Lebsack"
}
]
事件表
[
{
"eventId": 3,
"content": "hello",
"userId": 7
},
{
"eventId": 12,
"content": "rulsan berden",
"userId": 1
}
]
連接表
[
{
"name": "Hugo Powlowski",
"events": "rulsan berden"
},
{
"name": "Jeremy Littel II",
"events": null
},
{
"name": "Norman Zboncak",
"events": "hello"
}
]
uj5u.com熱心網友回復：
您應該按父表中的列進行分組，而不是按左連接的表，以便值永遠不會為空。
所以GROUP BY userid改為GROUP BY users.id.
uj5u.com熱心網友回復：
嘗試使用嵌套SELECT，這應該回傳null 沒有users任何事件：
select
u.name,
SELECT(
group_concat(content)
FROM
events
WHERE
userId = u.id
) as events
from
users u
order by
u.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/470342.html
上一篇：如何選擇總價值最高的物件組
下一篇：僅顯示json回應中的某些鍵