mysql - 显示同一生日的名字 MySQL

Question

我正在尝试查询显示同一天出生的音乐家

我有下表

人

person_id
姓氏
给定名称
出生日期

这是我目前拥有的代码

SELECT P.birthDate, DATE_FORMAT(birthDate,'%d %b %Y'), CONCAT(familyName, " ",givenName)
AS Person
FROM person P
WHERE givenName IS NOT NULL
AND familyName IS NOT NULL
AND birthDate IS NOT NULL
AND DATE_FORMAT(birthDate,'%d %b %Y') IS NOT NULL
ORDER BY DATE_FORMAT(birthDate,'%d %b %Y');

我似乎无法弄清楚如何使用 where 或其他子句来执行此操作。

Answer 1

给您一些选择:

1) 分组依据

SELECT P.birthDate, DATE_FORMAT(birthDate,'%d %b %Y'), GROUP_CONCAT(CONCAT(familyName, " ",givenName)) AS PersonNames
  FROM person P
 WHERE concat( givenName, familyName, birthDate) IS NOT NULL
 GROUP BY P.birthDate;

2) 加入

SELECT P.person_id, P.birthDate, 
       DATE_FORMAT(P.birthDate,'%d %b %Y'), 
       CONCAT(P.familyName, " ",P.givenName) AS PersonName, 
       CONCAT(PB.familyName, " ",PB.givenName) AS OtherPersonName
  FROM person P
  LEFT JOIN person PB
    ON P.birthDate = PB.birthDate
 WHERE concat( P.givenName, P.familyName, P.birthDate) IS NOT NULL
   AND concat( PB.givenName, PB.familyName ) IS NOT NULL
 ORDER BY P.person_id,PB.person_id;