我有那些表:
派对
id | date | place
Party_Attendands
id | party_id | person_id | unknow_person_id
人数
id | name | lastname
我想对一些数据进行规范化处理,并避免使用Party_Attendands。基本上我想要一个像
党和党人
id | date | place | attendands_list
0 | 01/01/2016 | NY | 1,2,3,4
以下查询返回person_ids
,但不会返回unknow_person_ids
。导致聚会崩溃的人也是人:)。如果不清楚,unknow_person_ids
则仅是ID中不存在的ID Persons
。因此,每行Party_Attendands
包含一个person_id
或unknow_person_id
但不是两者。
因此attendands_list
应该包含person_ids
和unknow_person_ids
,目前仅处理前者。
SELECT party.party_id, party.date, attendands.list
FROM party
LEFT OUTER JOIN
(
SELECT party_id , GROUP_CONCAT( Party_Attendands.person_id
ORDER BY person_id
SEPARATOR ',' ) AS list
FROM Party_Attendands
GROUP BY Party_Attendands.party_id
) AS attendands
ON party.party_id = attendands.party_id
TL; DR使用UNION
。
您可以像这样将person_id和unknown_person_id值的并集。
SELECT party_id, person_id
FROM Party_Attendands
UNION
SELECT party_id, unknown_person_id AS person_id
FROM Party_Attendands
然后,您可以将这些项目分组,如下所示:
SELECT party_id,
GROUP_CONCAT(DISTINCT person_id ORDER BY person_id) persons
FROM (
SELECT party_id, person_id
FROM Party_Attendands
UNION
SELECT party_id, unknown_person_id AS person_id
FROM Party_Attendands
) a
GROUP BY party_id
然后,您可以将该结果连接到另一个表。
SELECT p.id party_id, p.date, p.place, q.persons
FROM Party p
LEFT JOIN (
SELECT party_id,
GROUP_CONCAT(DISTINCT person_id ORDER BY person_id) persons
FROM (
SELECT party_id, person_id
FROM Party_Attendands
UNION
SELECT party_id, unknown_person_id
FROM Party_Attendands
) a
GROUP BY party_id
) q ON p.id = q.party_id
我想如果没有人出现,这不是聚会。但是,即使其他表中没有相应的行,LEFT JOIN
也将允许结果集显示Party
表中的行。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句