MySQL问题：将GROUP BY筛选的同品种同年龄狗名转为双列输出

已知dogs表结构：

create table dogs (name char(10), breed char(20), age int);

插入数据：

insert into dogs (name, breed, age) values ('A', 'Breed 1', 1);
insert into dogs (name, breed, age) values ('B', 'Breed 2', 2);
insert into dogs (name, breed, age) values ('C', 'Breed 3', 3);
insert into dogs (name, breed, age) values ('D', 'Breed 1', 4);
insert into dogs (name, breed, age) values ('E', 'Breed 2', 2);
insert into dogs (name, breed, age) values ('F', 'Breed 3', 5);
insert into dogs (name, breed, age) values ('G', 'Breed 1', 1);

需求是筛选出品种（breed）和年龄（age）均相同的狗，将名字分别放在name1和name2列输出，预期结果：

name1    name2
A        G
B        E

你当前的查询能筛选出符合条件的狗名，但要实现双列格式，可采用以下两种方法：

方法1：自连接实现配对 #

直接通过自连接匹配同品种、同年龄且名字不同的记录，同时用d1.name < d2.name避免重复配对（比如不会同时出现A-G和G-A）：

SELECT d1.name AS name1, d2.name AS name2
FROM dogs d1
JOIN dogs d2 
  ON d1.breed = d2.breed 
  AND d1.age = d2.age 
  AND d1.name < d2.name;

执行后会直接得到预期结果。

方法2：窗口函数+条件聚合 #

如果后续可能出现同品种同年龄的狗多于2只的情况，这种方法扩展性更强：

WITH ranked_dogs AS (
  SELECT 
    name, 
    breed, 
    age,
    -- 按品种、年龄分组，给组内名字排序编号
    ROW_NUMBER() OVER(PARTITION BY breed, age ORDER BY name) AS rn
  FROM dogs
  -- 先筛选出存在重复品种+年龄的记录
  WHERE (breed, age) IN (
    SELECT breed, age 
    FROM dogs 
    GROUP BY breed, age 
    HAVING COUNT(*) > 1
  )
)
-- 按品种、年龄分组，将不同编号的名字映射到对应列
SELECT 
  MAX(CASE WHEN rn = 1 THEN name END) AS name1,
  MAX(CASE WHEN rn = 2 THEN name END) AS name2
FROM ranked_dogs
GROUP BY breed, age;

该方法通过窗口函数给每组内的狗名编号，再用条件聚合将编号1和2的名字分别放入name1和name2列，即使某组有更多狗，也能按顺序提取对应名字。

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