MySQL无公共列横向合并表：sakila actor姓氏分类查询替代方案

我在MySQL的sakila数据库中练习，其中actor表包含last_name字段。需求是：将仅出现一次的last_name放在not_repeated列，出现多次的放在repeated列；如果某列行数更多，另一列对应行填充null。我已经用row_number()实现了一个方案，现在想找其他可行的SQL写法。

表结构及测试数据 #

-- 表定义
CREATE TABLE actor
(
    actor_id    smallint unsigned auto_increment
        primary key,
    first_name  varchar(45)                         not null,
    last_name   varchar(45)                         not null,
    last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
)
    charset = utf8;

CREATE INDEX idx_actor_last_name
    ON actor (last_name);

-- 插入测试数据
INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES  (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'),
        (2, 'NICK', 'BERGEN', '2006-02-15 04:34:33'),
        (3, 'ED', 'BERRY', '2006-02-15 04:34:33'),
        (4, 'JENNIFER', 'DAVIS', '2006-02-15 04:34:33'),
        (5, 'JOHNNY', 'OLIVIER', '2006-02-15 04:34:33'),
        (6, 'BETTE', 'GABLE', '2006-02-15 04:34:33'),
        (7, 'GRACE', 'MOSTEL', '2006-02-15 04:34:33'),
        (8, 'MATTHEW', 'GABLE', '2006-02-15 04:34:33'),
        (9, 'JOE', 'SWANK', '2006-02-15 04:34:33'),
        (10, 'CHRISTIAN', 'GABLE', '2006-02-15 04:34:33'),
        (11, 'ZERO', 'CAGE', '2006-02-15 04:34:33'),
        (12, 'KARL', 'BERRY', '2006-02-15 04:34:33'),
        (13, 'UMA', 'WOOD', '2006-02-15 04:34:33'),
        (14, 'VIVIEN', 'BERGEN', '2006-02-15 04:34:33'),
        (15, 'CUBA', 'OLIVIER', '2006-02-15 04:34:33');

预期输出 #

现有解决方案（基于row_number()） #

WITH group_last_name AS
         (SELECT last_name,
                 CASE
                     WHEN count(*) > 1 THEN 'many'
                     ELSE 'once'
                     END AS frequency
          FROM actor
          GROUP BY last_name),
     onceTbl AS
         (SELECT last_name,
                 row_number() OVER (PARTITION BY frequency) AS idx
          FROM group_last_name
          WHERE frequency = 'once'),
     manyTbl AS
         (SELECT last_name,
                 row_number() OVER (PARTITION BY frequency) AS idx
          FROM group_last_name
          WHERE frequency = 'many')
SELECT onceTbl.last_name AS not_repeated,
       manyTbl.last_name AS repeated
FROM manyTbl
         LEFT OUTER JOIN onceTbl ON onceTbl.idx = manyTbl.idx
UNION
SELECT onceTbl.last_name AS not_repeated,
       manyTbl.last_name AS repeated
FROM onceTbl
         LEFT OUTER JOIN manyTbl ON onceTbl.idx = manyTbl.idx;

其他可行实现方法 #

方法一：使用用户变量生成行号（兼容MySQL 5.x版本） #

如果你的MySQL版本不支持窗口函数（8.0以下），可以用用户变量手动生成行号：

-- 1. 统计每个last_name的出现次数，临时表存储
CREATE TEMPORARY TABLE name_counts AS
SELECT last_name, COUNT(*) AS cnt
FROM actor
GROUP BY last_name;

-- 2. 生成非重复last_name的带行号列表
CREATE TEMPORARY TABLE not_repeated_list AS
SELECT last_name, @row1 := @row1 + 1 AS idx
FROM name_counts, (SELECT @row1 := 0) AS init
WHERE cnt = 1
ORDER BY last_name;

-- 3. 生成重复last_name的带行号列表
CREATE TEMPORARY TABLE repeated_list AS
SELECT last_name, @row2 := @row2 + 1 AS idx
FROM name_counts, (SELECT @row2 := 0) AS init
WHERE cnt > 1
ORDER BY last_name;

-- 4. 连接两个列表，获取最终结果
SELECT nr.last_name AS not_repeated, r.last_name AS repeated
FROM not_repeated_list nr
LEFT JOIN repeated_list r ON nr.idx = r.idx
UNION
SELECT nr.last_name AS not_repeated, r.last_name AS repeated
FROM repeated_list r
LEFT JOIN not_repeated_list nr ON r.idx = nr.idx
ORDER BY not_repeated, repeated;

方法二：简化窗口函数写法 #

基于窗口函数，但简化CTE结构，减少子查询层级：

WITH once_names AS (
    SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) AS idx
    FROM actor
    GROUP BY last_name
    HAVING COUNT(*) = 1
),
repeated_names AS (
    SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) AS idx
    FROM actor
    GROUP BY last_name
    HAVING COUNT(*) > 1
)
-- 用LEFT JOIN + UNION模拟FULL OUTER JOIN
SELECT o.last_name AS not_repeated, r.last_name AS repeated
FROM once_names o
LEFT JOIN repeated_names r ON o.idx = r.idx
UNION
SELECT o.last_name AS not_repeated, r.last_name AS repeated
FROM repeated_names r
LEFT JOIN once_names o ON r.idx = o.idx
ORDER BY not_repeated, repeated;

方法三：利用GROUP BY和条件聚合 #

通过分组行号，将两类last_name聚合到同一行：

WITH all_names AS (
    SELECT 
        last_name,
        COUNT(*) OVER (PARTITION BY last_name) AS cnt,
        -- 分别给两类名字生成独立的行号
        CASE WHEN COUNT(*) OVER (PARTITION BY last_name) = 1 
             THEN ROW_NUMBER() OVER (ORDER BY last_name) END AS once_idx,
        CASE WHEN COUNT(*) OVER (PARTITION BY last_name) > 1 
             THEN ROW_NUMBER() OVER (ORDER BY last_name) END AS many_idx
    FROM actor
    GROUP BY last_name
)
SELECT 
    MAX(CASE WHEN cnt = 1 THEN last_name END) AS not_repeated,
    MAX(CASE WHEN cnt > 1 THEN last_name END) AS repeated
FROM all_names
-- 用COALESCE统一行号，确保两类名字按位置对齐
GROUP BY COALESCE(once_idx, many_idx)
ORDER BY COALESCE(once_idx, many_idx);

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