Dalam Mysql, batch menambah berbilang lajur pada jadual berdasarkan lajur lain

Question

Saya mempunyai jadual dengan tiga lajur. Untuk setiap id, kami mempunyai sehingga 400 nilai index Saya ingin menambah lajur berdasarkan bilangan indeks. Dalam contoh yang saya berikan, saya mempunyai 4 indeks dan kemudian saya menambah empat lajur pada jadual. Inilah jadual yang saya ada:

Buat jadual beli_jual (id int, idx varchar(255), jual float(2, 1));

masukkan ke dalam nilai jual_beli (id, idx, sell) ('1', 'a', '4');
masukkan ke dalam nilai beli_jual (id, idx, jual) ('1', 'b', '6');
masukkan ke dalam nilai jual_beli (id, idx, sell) ('1', 'c', '8');
masukkan ke dalam nilai beli_jual (id, idx, jual) ('1', 'd', '9');
masukkan ke dalam nilai beli_jual (id, idx, jual) ('3', 'b', '1');
masukkan ke dalam nilai beli_jual (id, idx, jual) ('3', 'c', '2');
masukkan ke dalam nilai beli_jual (id, idx, jual) ('2', 'a', '5');
masukkan ke dalam nilai jual_beli (id, idx, sell) ('2', 'b', '7');
masukkan ke dalam nilai beli_jual (id, idx, jual) ('2', 'd', '5');
PILIH * DARI buy_sell;

Inilah hasilnya:

id	idx	Untuk Dijual
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

Sebagai contoh, untuk id=1, kami mempunyai empat indeks di sini (a, b, c, d), dan kemudian kami mempunyai empat lajur bukan sifar.Untuk id = 3 kita mempunyai dua indeks (b, c) dan kemudian kita mempunyai dua lajur bukan sifar, jadi untuk lajur pertama kita letak sifar, untuk lajur kedua kita letak 1 dan untuk lajur ketiga kita Letakkan 2. Begitu seterusnya dan seterusnya. Inilah jadual yang saya mahu:

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

Saya banyak mencari dan mencuba Group_concat, JSON_ARRAYAGG tetapi saya tidak dapat mencari penyelesaian. apa yang perlu saya buat?

Answer 1

Bahasa SQL mempunyai keperluan yang sangat ketat untuk mengetahui bilangan lajur dalam hasil pada masa penyusunan pertanyaan, sebelum melihat sebarang data. Jika anda perlu melihat data untuk menentukan bilangan lajur yang anda inginkan, maka anda hanya boleh menggunakan SQL dinamik (berpotensi berbahaya), yang memerlukan tiga langkah:

1. Jalankan pertanyaan untuk mencari maklumat tentang lajur yang diperlukan.
2. Bina pernyataan SQL baharu secara dinamik menggunakan keputusan langkah 1.
3. Jalankan SQL dari langkah 2.

Dalam kes ini, anda tidak tahu berapa banyak lajur yang diperlukan, cuma ia adalah "sehingga 400 lajur". Dengan mengambil kira perkara ini, anda mungkin melihat kod seperti ini:

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

Ya, anda perlu menentukan sesuatu dalam pertanyaan untuk setiap lajur yang mungkin. Ini juga menganggap sell值都大于0。如果你的值可能是正数和负数的混合，你可以尝试使用SUM()而不是MAX() anda.

Pendekatan ini juga secara langsung bertentangan dengan prinsip teori yang ditetapkan di sebalik pangkalan data hubungan, jadi secara praktikal anda lebih baik membiarkan kod pelanggan atau alat pelaporan melakukan pemutaran pada data.