Fügen Sie in MySQL mehrere Spalten basierend auf einer anderen Spalte stapelweise zur Tabelle hinzu

Question

Ich habe eine Tabelle mit drei Spalten. Für jede ID haben wir bis zu 400 index-Werte. Ich möchte Spalten basierend auf der Anzahl der Indizes hinzufügen. In dem von mir bereitgestellten Beispiel habe ich vier Indizes und habe dann vier Spalten zur Tabelle hinzugefügt. Dies ist die Tabelle, die ich habe:

Tabelle buy_sell erstellen (id int, idx varchar(255), sale float(2, 1));

in buy_sell (id, idx, sale) Werte einfügen ('1', 'a', '4');
in buy_sell (id, idx, sale) Werte einfügen ('1', 'b', '6');
in buy_sell (id, idx, sale) Werte einfügen ('1', 'c', '8');
in buy_sell (id, idx, sale) Werte einfügen ('1', 'd', '9');
in buy_sell (id, idx, sale) Werte einfügen ('3', 'b', '1');
in buy_sell (id, idx, sale) Werte einfügen ('3', 'c', '2');
in buy_sell (id, idx, sale) Werte einfügen ('2', 'a', '5');
in buy_sell (id, idx, sale) Werte einfügen ('2', 'b', '7');
in buy_sell (id, idx, sale) Werte einfügen ('2', 'd', '5');
SELECT * FROM buy_sell;

Das ist das Ergebnis:

id	idx	Zu verkaufen
1	a	4.0
1	b	6.0
1	c	8.0
1	d	9.0
3	b	1.0
3	c	2.0
2	a	5.0
2	b	7.0
2	d	5.0

Für id=1 haben wir hier beispielsweise vier Indizes (a, b, c, d) und dann vier Spalten ungleich Null.Für id = 3 haben wir zwei Indizes (b, c) und dann zwei Spalten ungleich Null, also geben wir für die erste Spalte eine Null, für die zweite Spalte eine 1 und für die dritte Spalte eine 2 ein. Und so weiter und so fort. Dies ist die Tabelle, die ich möchte:

id	1 verkaufen	2 verkaufen	3 verkaufen	4 verkaufen
1	4	6	8	9
3	0	1	2	0
2	5	7	0	5

Ich habe viel gesucht und Group_concat, JSON_ARRAYAGG usw. ausprobiert, konnte aber keine Lösung finden. was muss ich tun?

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

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