MySQL - Tukar baris kepada lajur

Question

Saya cuba mencari siaran tetapi hanya menemui penyelesaian untuk SQL Server/Access. Saya memerlukan penyelesaian dalam MySQL (5.X).

Saya mempunyai jadual (dipanggil sejarah) dengan 3 lajur: hos, nama item, nilai item.

Jika saya melakukan pilihan (select * from History) ia kembali

+--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    C     |    40     |
   +--------+----------+-----------+

Cara membuat pertanyaan pangkalan data untuk mengembalikan kandungan yang serupa

+--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

Answer 1

Saya akan menambah penerangan yang lebih panjang dan terperinci tentang langkah-langkah untuk menyelesaikan isu ini. Saya minta maaf jika terlalu panjang.

---

Saya akan mulakan dengan asas yang anda berikan dan menggunakannya untuk menentukan beberapa istilah yang akan saya gunakan sepanjang artikel ini. Ini akan menjadi jadual asas:

select * from history;

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      1 | B        |         3 |
|      2 | A        |         9 |
|      2 | C        |        40 |
+--------+----------+-----------+

Ini akan menjadi matlamat kami, Nice Pivot Table:

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

Nilai dalam lajur

history.hostid akan menjadi nilai history.hostid 列中的值将成为数据透视表中的y 值。 history.itemnamey dalam jadual pangsi. Nilai dalam lajur history.itemname akan menjadi x-values

(atas sebab yang jelas).

---

Apabila saya perlu menyelesaikan masalah mencipta jadual pangsi, saya menyelesaikannya menggunakan proses tiga langkah (dengan langkah keempat pilihan):

1. Pilih lajur yang diminati, iaitu nilai y dan nilai x
2. Lanjutkan jadual asas dengan lajur tambahan - satu lajur untuk setiap nilai x
3. Kumpulkan dan agregat jadual lanjutan - satu set setiap nilai
y
(Pilihan) Cantikkan jadual agregat

Mari gunakan langkah ini untuk masalah anda dan lihat apa yang kami dapat:

Langkah 1: Pilih lajur yang diminatihostid 提供y 值，itemname. Dalam hasil yang diingini, hostid memberikan nilai y dan

memberikan nilai

x . itemname

Langkah 2: Panjangkan jadual asas dengan lajur tambahan

. Kami biasanya mahu satu lajur untuk setiap nilai x. Ingat bahawa lajur x-value kami ialah NULL 的模式 - itemname = "A" 的行的新列 A:

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

Perhatikan bahawa kami tidak menukar bilangan baris - kami hanya menambah lajur tambahan. Juga ambil perhatian bahawa mempunyai nilai bukan nol dan lajur baharu yang lain mempunyai nilai nol. 按 hostid 分组

Langkah 3: Kumpulkan dan agregat jadual lanjutan

. Kami memerlukan kerana ia memberikan nilai y:

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(A) as A,
    sum(B) as B,
    sum(C) as C
  from history_extended
  group by hostid
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

NULL (Perhatikan bahawa kita kini mempunyai satu baris setiap nilai y.)

Okay, kita hampir sampai! Kita cuma perlu singkirkan yang hodoh .

Langkah 4: Cantikkan

. Kami akan menggantikan mana-mana nilai nol dengan sifar supaya set hasil kelihatan lebih baik:

---

create view history_itemvalue_pivot_pretty as (
  select 
    hostid, 
    coalesce(A, 0) as A, 
    coalesce(B, 0) as B, 
    coalesce(C, 0) as C 
  from history_itemvalue_pivot 
);

select * from history_itemvalue_pivot_pretty;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

Selesai – kami telah membina jadual pangsi yang cantik menggunakan MySQL.

🎜 🎜Perkara yang perlu diambil perhatian apabila menggunakan proses ini:🎜

• Apakah nilai untuk digunakan dalam lajur tambahan. Saya menggunakan itemvalue
dalam contoh ini
• Apakah nilai "neutral" ​​untuk digunakan dalam lajur tambahan. Saya menggunakan NULL，但也可能是 0 或 "", ia bergantung pada situasi khusus anda
• Apakah fungsi agregat untuk digunakan semasa mengumpulkan. Saya menggunakan sum，但是 count 和 max 也经常使用（max sering digunakan apabila membina satu baris) "objek" diedarkan merentasi berbilang baris)
• Gunakan berbilang lajur untuk mewakili nilai y. Penyelesaian ini tidak terhad kepada menggunakan satu lajur untuk nilai y - hanya masukkan lajur tambahan group by 子句（并且不要忘记 selectmereka)

Batasan yang diketahui:

• Penyelesaian ini tidak membenarkan n lajur dalam jadual pangsi - setiap lajur pangsi perlu ditambah secara manual apabila memanjangkan jadual asas. Jadi untuk 5 atau 10 x nilai penyelesaian ini adalah baik. 100 yuan, tidak begitu baik. Terdapat beberapa penyelesaian menggunakan prosedur tersimpan untuk menjana pertanyaan, tetapi ia adalah hodoh dan sukar untuk dilakukan dengan betul. Pada masa ini saya tidak tahu cara yang baik untuk menyelesaikan masalah ini apabila jadual pangsi perlu mempunyai banyak lajur.