mysql - 输出名称字段与没有名称字段的输出不同 MYSQL

Question

SELECT users.id as uid,   projects.id as pid
            FROM users  
            inner join usergroup on usergroup.id = users.user_group
            inner join bookings on bookings.agent_id = users.id 
            inner join units on bookings.unit = units.id
            inner join types on types.id = units.types_id 
            inner join projects on projects.id = types.project_id
            WHERE bookings.status = 'Accepted' AND units.status = 'Sold'
            GROUP BY  pid

通过上面的查询，我得到了正确的逻辑输出:

pid   |   uid
1          1
2          1
9          12
10         14

然后我想显示用户的名字，所以我在查询中添加了1个字段，如下所示:

SELECT users.id as uid,  users.f_name, projects.id as pid
            FROM users  
            inner join usergroup on usergroup.id = users.user_group
            inner join bookings on bookings.agent_id = users.id 
            inner join units on bookings.unit = units.id
            inner join types on types.id = units.types_id 
            inner join projects on projects.id = types.project_id
            WHERE bookings.status = 'Accepted' AND units.status = 'Sold'
            GROUP BY  pid

但我得到了不同的 uid 输出:

pid    |    uid
1            1
2            1
9            9
10           11

这不符合逻辑，有人知道为什么吗？假设两者都是正确的，但为什么会输出不同的结果？

Answer 1

您的第二个查询是在 where 条件之前使用连接条件过滤数据，而在第一个查询中您是在 where 子句中过滤数据...使第二个查询与第一个查询相同以获得相同的结果...

尝试下面的查询-

SELECT projects.id as pid, users.id as uid, users.f_name
            FROM users  
            inner join usergroup on usergroup.id = users.user_group
            inner join bookings on bookings.agent_id = users.id 
            inner join units on bookings.unit = units.id  
            inner join types on types.id = units.types_id 
            inner join projects on projects.id = types.project_id
            WHERE bookings.status = 'Accepted' AND units.status = 'Sold'
            GROUP BY  pid