查询加入表时，是否可以返回无效行

发布于 2025-01-25 21:10:05 字数 1269 浏览 2 评论 0原文

我有两个表：用户带有ID> ID的表代码>，content和用户ID列。

我正在尝试查询一张表，该表返回与name和Events列中Events的 contry 列的信息将表示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"
}
]

原文

I have two tables: users table with id, name columns and events table with id, content and userId columns.

I am trying to query a table that return joined information from these two tables with name and events columns where events would represent an array of content fields corresponding to a user.

This is the query I am running:

select 
    name, group_concat(content) as events 
from 
    users 
left join 
    events on id = userId 
group by 
    userId 
order by 
    id

However rows with null values are not being returned except of just one row. What am I doing wrong?

Users table

[
{
"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"
}
]

Events table

[
{
"eventId": 3,
"content": "hello",
"userId": 7
},
{
"eventId": 12,
"content": "rulsan berden",
"userId": 1
}
]

Joined table

[
{
"name": "Hugo Powlowski",
"events": "rulsan berden"
},
{
"name": "Jeremy Littel II",
"events": null
},
{
"name": "Norman Zboncak",
"events": "hello"
}
]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

家住魔仙堡 2025-02-01 21:10:05

您应该按父表中的列对列进行分组，而不是剩下的表连接，以免值为null。

因此，用户ID 将组更改为by User.id组。

回复收藏 0 原文

静若繁花 2025-02-01 21:10:05

尝试使用嵌套的选择，这应该返回null＆nbsp; for users而无需任何事件：

select 
    u.name, 
    SELECT(
      group_concat(content)  
        FROM 
      events 
        WHERE 
      userId = u.id
    ) as events 
from 
    users u
order by 
    u.id

Try to use a nested SELECT, this should return null for the users without any event:

select 
    u.name, 
    SELECT(
      group_concat(content)  
        FROM 
      events 
        WHERE 
      userId = u.id
    ) as events 
from 
    users u
order by 
    u.id

回复收藏 0 原文

~没有更多了~