mysql - 在 MYSQL 中选择行作为列

Question

你好，我想弄清楚如何做到这一点，我有一个 mysql 表

| ID |    ACC_ID  | line_id | code     |
| 1  |          1 |    5960 | DCA      |
| 2  |          1 |    5960 | AAA      |
| 3  |          1 |    5960 | DDD      | 
| 4  |          1 |    5960 | DER      |
| 5  |          1 |    5054 | DCB      |
| 6  |          1 |    5054 | AAC      |
| 7  |          1 |    5011 | DDE      |
| 8  |          1 |    5012 | DEQ      |

等数据库下降了大约 10000 行

我想创建一个 mysql select 语句来执行此操作

| ACC_ID     | line_id | code     | code     | code     | code     | 
|   1        | 5960    | DCA      | AAA      | DDD      | DER      | 
|   1        | 5054    | DCB      | DER      |          |          |
|   1        | 5011    | DDE      |          |          |          |
|   1        | 5012    | DEQ      |          |          |          |

每个 line_id 最多可以有十个代码

现在我的问题是可以使用 select 语句进行上述查询。

谢谢大家的帮助

Answer 1

这是一个PIVOT，但MySQL 没有PIVOT 函数，但您可以使用聚合函数和CASE 语句复制它。 MySQL 也没有按组分配行号的最简单方法，但以下是如何使用 SQL 实现此目的的示例。既然你说过每个 line_id 最多可以有 10 个代码，我就硬编码了一个可能的解决方案。:

select acc_id,
  line_id,
  max(case when group_row_number = 1 then code end) Code1,
  max(case when group_row_number = 2 then code end) Code2,
  max(case when group_row_number = 3 then code end) Code3,
  max(case when group_row_number = 4 then code end) Code4,
  max(case when group_row_number = 5 then code end) Code5,
  max(case when group_row_number = 6 then code end) Code6,
  max(case when group_row_number = 7 then code end) Code7,
  max(case when group_row_number = 8 then code end) Code8,
  max(case when group_row_number = 9 then code end) Code9,
  max(case when group_row_number = 10 then code end) Code10
from
(
  select ACC_ID,
    line_id,
    code, 
    @num := if(@ACC_ID = `ACC_ID` AND @line_id = `line_id`, @num + 1, 1) as group_row_number,
    @ACC_ID := `ACC_ID` as dummy,
    @line_id := `line_id` as linedummy
  from yourtable
) src
group by acc_id, line_id
order by line_id desc

参见 SQL Fiddle with Demo

结果:

| ACC_ID | LINE_ID | CODE1 |  CODE2 |  CODE3 |  CODE4 |  CODE5 |  CODE6 |  CODE7 |  CODE8 |  CODE9 | CODE10 |
-------------------------------------------------------------------------------------------------------------
|      1 |    5960 |   DCA |    AAA |    DDD |    DER | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |    5054 |   DCB |    AAC | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |    5012 |   DEQ | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |    5011 |   DDE | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |