MySQL查询如何按firstName和surName分组？获取无空值的4行结果

你的查询返回8行重复数据的原因是使用了JOIN addresses，每个用户在addresses表中有2条记录（B和D类型），关联后会生成2倍行数；虽然子查询能正确获取对应类型的地址，但重复的JOIN导致结果重复输出。以下是三种可行的解决方案：

方案1：移除不必要的JOIN，直接从users表查询 #

子查询已经通过user_id = u.ID关联了地址表，无需额外JOIN addresses，每个用户仅返回一行结果：

SELECT  u.firstName firstName, u.surName surName  ,  
(SELECT  a.city from addresses   WHERE a.type = 'B' and a.user_id = u.ID  limit 1 ) as BILLING_CITY,
(SELECT  a.street from addresses   WHERE  a.type = 'B' and a.user_id = u.ID  limit 1) as BILLING_STREET ,
(SELECT  a.country from addresses   WHERE  a.type = 'B' and a.user_id = u.ID limit 1) as BILLING_COUNTRY ,
(SELECT  a.city from addresses   WHERE a.type = 'D' and a.user_id = u.ID limit 1) as  DELIVERY_CITY ,
(SELECT  a.street from addresses   WHERE  a.type = 'D' and a.user_id = u.ID limit 1) as  DELIVERY_STREET ,
(SELECT  a.country from addresses  WHERE  a.type = 'D' and a.user_id = u.ID  limit 1) as DELIVERY_COUNTRY
FROM users u;

方案2：分别关联B和D类型的地址表 #

通过两次JOIN地址表，分别匹配B（账单）和D（配送）类型的地址，确保每个用户一行结果：

SELECT
    u.firstName,
    u.surName,
    b.city AS BILLING_CITY,
    b.street AS BILLING_STREET,
    b.country AS BILLING_COUNTRY,
    d.city AS DELIVERY_CITY,
    d.street AS DELIVERY_STREET,
    d.country AS DELIVERY_COUNTRY
FROM users u
INNER JOIN addresses b ON u.ID = b.user_id AND b.type = 'B'
INNER JOIN addresses d ON u.ID = d.user_id AND d.type = 'D';

方案3：使用条件聚合（GROUP BY） #

通过GROUP BY用户字段，结合聚合函数（如MAX）提取对应类型的地址，适合用户存在多个同类型地址的场景：

SELECT
    u.firstName,
    u.surName,
    MAX(CASE WHEN a.type = 'B' THEN a.city END) AS BILLING_CITY,
    MAX(CASE WHEN a.type = 'B' THEN a.street END) AS BILLING_STREET,
    MAX(CASE WHEN a.type = 'B' THEN a.country END) AS BILLING_COUNTRY,
    MAX(CASE WHEN a.type = 'D' THEN a.city END) AS DELIVERY_CITY,
    MAX(CASE WHEN a.type = 'D' THEN a.street END) AS DELIVERY_STREET,
    MAX(CASE WHEN a.type = 'D' THEN a.country END) AS DELIVERY_COUNTRY
FROM users u
INNER JOIN addresses a ON u.ID = a.user_id
GROUP BY u.firstName, u.surName;

内容的提问来源于stack exchange，提问作者user8905822