如何使用选择查询的GROUP_CONCAT子查询在MySQL
问题描述:
我有3个表:如何使用选择查询的GROUP_CONCAT子查询在MySQL
tbl_user存储所有用户信息(USER_ID,姓名,地址)
user_id name address
1 a (shop) home
2 b (shop) bakerstreet
3 c (staff) wallstreet
4 d (staff) georgia
5 e (staff) sydney
tbl_user_group存储用户类型(USER_ID, USER_TYPE:1 = shop_owner,2 =人员)
user_id user_type
1 1
2 1
3 2
4 2
5 2
tbl_user_association保持shop_owner和工作人员的关系(shop_owner_id,staff_id)
shop_owner_id staff_id
1 3
1 4
1 5
2 3
2 4
期望结果
我想显示员工和它们与如下相关联的相应店铺的列表:
user_id staff_name shop_owner
3 c a,b
4 d a,b
5 e a
我使用GROUP_CONCAT如上所述here试过。查询如下:
SELECT
u.id AS user_id,
u.name AS staff_name,
(SELECT GROUP_CONCAT(u.name separator ',') FROM tbl_user u WHERE u.id = ua.shop_owner_id) AS
shop_owner
FROM tbl_user u
JOIN tbl_user_group ug ON u.id = ug.user_id
LEFT JOIN tbl_user_association ua ON u.id = ua.staff_id
WHERE ug.user_type = 2
GROUP BY u.id
但它返回如下人员的单行。我哪里出错了?
user_id staff_name shop_owner
3 c a
4 d a
5 e a
答
这是我会怎么做它:
SELECT
u.user_id,
u.name,
GROUP_CONCAT(so.name)
FROM
tbl_user_group ug
INNER JOIN tbl_user u
ON (ug.user_id = u.user_id)
INNER JOIN tbl_user_association ua
ON (ua.staff_id = u.user_id)
INNER JOIN tbl_user so -- here join again the user table to get shop owners names
ON (ua.shop_owner_id = so.user_id)
WHERE
ug.user_type = 2
GROUP BY
u.user_id;
更换'(SELECT GROUP_CONCAT(u.name分离 '')FROM tbl_userüWHERE u.id = ua.shop_owner_id)AS shop_owner' with'(GROUP_CONCAT(u.name separator''))AS shop_owner' –
以及where子句'u.id = ua.shop_owner_id' –