我有3张桌子:
tbl_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=staff)
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 。查询如下:
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句