在Mysql中，根据另一列向表中批量添加多个列

Question

我有一个有三列的表格。 对于每个id，我们有多达400个index值。我想根据index的数量添加列。在我提供的示例中，我有4个index，然后我在表格中添加了四列。这是我拥有的表格：

Create table  buy_sell (id int, idx varchar(255), sell float(2, 1));

insert into buy_sell (id, idx,  sell) values ('1', 'a',  '4');
insert into buy_sell (id, idx,  sell) values ('1', 'b',  '6');
insert into buy_sell (id, idx,  sell) values ('1', 'c',  '8');
insert into buy_sell (id, idx,  sell) values ('1', 'd',  '9');
insert into buy_sell (id, idx,  sell) values ('3', 'b ', '1');
insert into buy_sell (id, idx,  sell) values ('3', 'c ', '2');
insert into buy_sell (id, idx,  sell) values ('2', 'a',  '5');
insert into buy_sell (id, idx,  sell) values ('2', 'b',  '7');
insert into buy_sell (id, idx,  sell) values ('2', 'd',  '5');
SELECT * FROM buy_sell;

这是结果：

id	idx	出售
1	一个	4.0
1	b	6.0
1	c	8.0
1	d	9.0
3	b	1.0
3	c	2.0
2	一个	5.0
2	b	7.0
2	d	5.0

例如，对于id=1，我们在这里有四个index（a，b，c，d），然后我们有四个非零列。对于id = 3，我们有两个index（b，c），然后我们有两个非零列，所以对于第一列，我们放置零，对于第二列，我们放置1，对于第三列，我们放置2。依此类推。这是我想要的表格：

id	卖出1	卖2	卖3	卖4
1	4	6	8	9
3	0	1	2	0
2	5	7	0	5

我搜索了很多，并尝试了Group_concat，JSON_ARRAYAGG等，但我找不到解决方法。我需要做什么？

Answer 1

SQL语言对于在查询编译时知道结果中的列数有非常严格的要求，在查看任何数据之前。如果你必须查看数据来确定你想要的列数，那么你只能使用（潜在危险的）动态SQL，需要三个步骤：

1. 运行一个查询来查找关于所需列的信息。
2. 使用步骤1的结果动态构建一个新的SQL语句。
3. 运行步骤2中的SQL。

在这种情况下，你不知道需要多少列，只知道它是“最多400列”。考虑到这一点，你可能会看到像这样的代码：

SELECT ID, 
     MAX(CASE WHEN IDX = 'a' THEN sell ELSE 0 END) as sell1, 
     MAX(CASE WHEN IDX = 'b' THEN sell ELSE 0 END) as sell2, 
     MAX(CASE WHEN IDX = 'c' THEN sell ELSE 0 END) as sell3,
     -- ... 
     MAX(CASE WHEN IDX = '??' THEN sell ELSE 0 END) as sell400
FROM `buy_sell`
GROUP BY ID

是的，你确实需要在查询中为每个可能的列指定一些内容。这也假设你的sell值都大于0。如果你的值可能是正数和负数的混合，你可以尝试使用SUM()而不是MAX()。

这种做法也与关系数据库背后的集合论原则直接相悖，因此在实践中，你最好让客户端代码或报表工具来对数据进行透视。