从原始数据提取列值：双表关联实现多参数转宽表需求

嗨，这个需求就是典型的行转列场景嘛！我给你整理了几种常用数据库的实现方案，直接抄就能用~

首先得给每个item下的参数按顺序排个序号（比如第1个、第2个、第3个参数），然后把序号对应的参数映射成单独的列，最后按item name分组聚合就行。

MySQL 实现 #

适合MySQL 8.0+版本（支持窗口函数） #

SELECT 
    t1.`item name`,
    MAX(CASE WHEN rn = 1 THEN t2.`item parameter` END) AS `item parameter 1`,
    MAX(CASE WHEN rn = 2 THEN t2.`item parameter` END) AS `item parameter 2`,
    MAX(CASE WHEN rn = 3 THEN t2.`item parameter` END) AS `item parameter 3`
FROM table1 t1
JOIN (
    SELECT 
        `item name`,
        `item parameter`,
        ROW_NUMBER() OVER (PARTITION BY `item name` ORDER BY `item parameter`) AS rn
    FROM table2
) t2 ON t1.`item name` = t2.`item name`
GROUP BY t1.`item name`;

适合MySQL 5.x版本（用变量生成序号） #

SELECT 
    t1.`item name`,
    MAX(CASE WHEN rn = 1 THEN t2.`item parameter` END) AS `item parameter 1`,
    MAX(CASE WHEN rn = 2 THEN t2.`item parameter` END) AS `item parameter 2`,
    MAX(CASE WHEN rn = 3 THEN t2.`item parameter` END) AS `item parameter 3`
FROM table1 t1
JOIN (
    SELECT 
        `item name`,
        `item parameter`,
        @rn := IF(@prev_item = `item name`, @rn + 1, 1) AS rn,
        @prev_item := `item name`
    FROM table2, (SELECT @rn := 0, @prev_item := '') vars
    ORDER BY `item name`, `item parameter`
) t2 ON t1.`item name` = t2.`item name`
GROUP BY t1.`item name`;

PostgreSQL 实现 #

方法1：通用CASE表达式写法 #

SELECT 
    t1."item name",
    MAX(CASE WHEN rn = 1 THEN t2."item parameter" END) AS "item parameter 1",
    MAX(CASE WHEN rn = 2 THEN t2."item parameter" END) AS "item parameter 2",
    MAX(CASE WHEN rn = 3 THEN t2."item parameter" END) AS "item parameter 3"
FROM table1 t1
JOIN (
    SELECT 
        "item name",
        "item parameter",
        ROW_NUMBER() OVER (PARTITION BY "item name" ORDER BY "item parameter") AS rn
    FROM table2
) t2 ON t1."item name" = t2."item name"
GROUP BY t1."item name";

方法2：用crosstab函数（更简洁） #

先确保安装tablefunc扩展，再执行查询：

-- 安装扩展（只需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT 
    "item name",
    "1" AS "item parameter 1",
    "2" AS "item parameter 2",
    "3" AS "item parameter 3"
FROM crosstab(
    'SELECT "item name", rn, "item parameter"
     FROM (
         SELECT 
             "item name",
             "item parameter",
             ROW_NUMBER() OVER (PARTITION BY "item name" ORDER BY "item parameter") AS rn
         FROM table2
     ) t
     WHERE rn <= 3
     ORDER BY 1, 2',
    'SELECT generate_series(1,3)'
) AS ct("item name" text, "1" text, "2" text, "3" text);

SQL Server 实现 #

方法1：用PIVOT函数（更直观） #

SELECT 
    "item name",
    [1] AS "item parameter 1",
    [2] AS "item parameter 2",
    [3] AS "item parameter 3"
FROM (
    SELECT 
        t1."item name",
        t2."item parameter",
        ROW_NUMBER() OVER (PARTITION BY t1."item name" ORDER BY t2."item parameter") AS rn
    FROM table1 t1
    JOIN table2 t2 ON t1."item name" = t2."item name"
) src
PIVOT (
    MAX("item parameter") FOR rn IN ([1], [2], [3])
) piv;

如果有些item的参数不足3个，对应的列会显示NULL。要是想替换成空字符串或者自定义默认值，把MAX(...)改成COALESCE(MAX(...), '')就行~

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