mysql - SQL 是否可以从 "list"进行 SELECT ？里面的例子

This question already has an answer here:

MySQL pivot row into dynamic number of columns

(1 个回答)


8年前关闭。




我的表格如下

name
| id | name |
|  1 | jon  |
|  2 | mary |

skill
| id | skill | level |
|  1 | C++   | 3     |
|  1 | Java  | 2     |
|  1 | HTML  | 5     |
|  1 | CSS   | 4     |
|  1 | JS    | 5     |
|  2 | PHP   | 4     |
|  2 | Ruby  | 3     |
|  2 | Perl  | 1     |

所以我希望输出是这样的:

| name | skill_1 | lv_1 | skill_2 | lv_2 | skill_3 | lv_3 | skill_4 | lv_4 | skill_5 | lv_5 |
| jon  | C++     | 3    | Java    | 2    | HTML    | 5    | CSS     | 4    | JS      | 5    |
| mary | PHP     | 4    | Ruby    | 3    | Perl    | 1    |         |      |         |      |

我将使用什么类型的 join 或 union 语句？每个人最多只有5个技能。

那么这个 SQL 会是什么样子呢？甚至有可能吗？

我完全迷失了，不知道从哪里开始。

最佳答案

由于您提到 Name 最多可以有 5 Skills ，因此可以使用静态查询来解决此问题。

-- <<== PART 2
SELECT  Name,
        MAX(CASE WHEN RowNumber = 1 THEN Skill END) Skill_1,
        MAX(CASE WHEN RowNumber = 2 THEN Skill END) Skill_2,
        MAX(CASE WHEN RowNumber = 3 THEN Skill END) Skill_3,
        MAX(CASE WHEN RowNumber = 4 THEN Skill END) Skill_4,
        MAX(CASE WHEN RowNumber = 5 THEN Skill END) Skill_5
FROM
        ( -- <<== PART 1
            SELECT  a.Name,
                    b.Skill,
                    (
                        SELECT  COUNT(*)
                        FROM    Skill c
                        WHERE   c.id = b.id AND
                                c.Skill <= b.Skill) AS RowNumber
            FROM    Name a
                    INNER JOIN Skill b
                        ON a.id = b.id
        ) x
GROUP   BY Name

SQLFiddle Demo

输出

╔══════╦═════════╦═════════╦═════════╦═════════╦═════════╗
║ NAME ║ SKILL_1 ║ SKILL_2 ║ SKILL_3 ║ SKILL_4 ║ SKILL_5 ║
╠══════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ jon  ║ C++     ║ CSS     ║ HTML    ║ Java    ║ JS      ║
║ mary ║ Perl    ║ PHP     ║ Ruby    ║ (null)  ║ (null)  ║
╚══════╩═════════╩═════════╩═════════╩═════════╩═════════╝

简要说明

让我们分解它。查询中有两个部分。

查询的第一部分，即 PART 1 ，为每个 Skill 生成 Name 上的数字序列。它只是使用相关子查询来模拟 ROW_NUMBER 不支持的窗口函数 MySQL。

Generating Sequential Number on Skill (SQLFiddle Demo)

第二部分 PART 2 根据在 PART 1 上生成的序列号将行转换为列。它使用 CASE 来测试数字的值并返回与数字关联的 Skill。如果数字不匹配，则返回 NULL 值。接下来，它使用 Name 聚合每组 MAX() 的列，因此如果有的话，将返回 SKILL 而不是 NULL。

更新 1

SELECT  Name,
        MAX(CASE WHEN RowNumber = 1 THEN Skill END) Skill_1,
        MAX(CASE WHEN RowNumber = 1 THEN Level END) Level_1,
        MAX(CASE WHEN RowNumber = 2 THEN Skill END) Skill_2,
        MAX(CASE WHEN RowNumber = 2 THEN Level END) Level_2,
        MAX(CASE WHEN RowNumber = 3 THEN Skill END) Skill_3,
        MAX(CASE WHEN RowNumber = 3 THEN Level END) Level_3,
        MAX(CASE WHEN RowNumber = 4 THEN Skill END) Skill_4,
        MAX(CASE WHEN RowNumber = 4 THEN Level END) Level_4,
        MAX(CASE WHEN RowNumber = 5 THEN Skill END) Skill_5,
        MAX(CASE WHEN RowNumber = 5 THEN Level END) Level_5
FROM
        (
            SELECT  a.Name,
                    b.Skill,
                    (
                        SELECT  COUNT(*)
                        FROM    Skill c
                        WHERE   c.id = b.id AND
                                c.skill <= b.skill) AS RowNumber,
                    b.Level
            FROM    Name a
                    INNER JOIN Skill b
                        ON a.id = b.id
        ) x
GROUP   BY Name

SQLFiddle Demo

输出

╔══════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╗
║ NAME ║ SKILL_1 ║ LEVEL_1 ║ SKILL_2 ║ LEVEL_2 ║ SKILL_3 ║ LEVEL_3 ║ SKILL_4 ║ LEVEL_4 ║ SKILL_5 ║ LEVEL_5 ║
╠══════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ jon  ║ C++     ║       3 ║ CSS     ║       4 ║ HTML    ║       5 ║ Java    ║ 2       ║ JS      ║ 5       ║
║ mary ║ Perl    ║       1 ║ PHP     ║       4 ║ Ruby    ║       3 ║ (null)  ║ (null)  ║ (null)  ║ (null)  ║
╚══════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╝

关于mysql - SQL 是否可以从 "list"进行 SELECT ？里面的例子，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16880781/