在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()。

這種做法也與關聯式資料庫背後的集合論原則直接相悖，因此在實務中，你最好讓客戶端程式碼或報表工具來對資料進行透視。