Nyahbina jadual MySQL

Question

Saya tertanya-tanya sama ada saya boleh menukar output ini kepada:

Pangkalan Data Pengguna Pilih Sisipkan Kemas Kini Padam Cipta Rujukan Alter Drop
------------- --------- ------ ------ - ----- ------ ---------- ------ --------
mysql.session performance_schema 1 0 0 0 0 0 0 0
mysql.sys sys 0 0 0 0 0 0 0 0

Ia menjadi seperti ini:

Sistem prestasi_schema Keistimewaan Pengguna
----- ---------- -------------------- ---
mysql.session Pilih 1
mysql.session Sisipkan 0
mysql.session Kemas Kini 0
mysql.session Padam 0
mysql.session Cipta 0
mysql.session Rujukan 0
mysql.session Alter 0
mysql.session Gugurkan 0
mysql.sys Pilih 0
mysql.sys Sisipkan 0
Kemas Kini mysql.sys 0
mysql.sys Padam 0
mysql.sys Cipta 0
mysql.sys Rujukan 0
mysql.sys Alter 0
mysql.sys Gugurkan 0

Pernyataan pertanyaan yang saya gunakan ialah:

SELECT
  BERBEZA
  PENGGUNA "Pengguna",
  db "Pangkalan Data",
  JIKA(Select_priv = 'Y', '1', '0') AS "Pilih",
  JIKA(Insert_priv = 'Y', '1', '0') AS "Sisipkan",
  IF(Update_priv = 'Y', '1', '0') AS "Kemas kini",
  JIKA(Delete_priv = 'Y', '1', '0') AS "Padam",
  JIKA(Create_priv = 'Y', '1', '0') AS "Create",
  IF(References_priv = 'Y', '1', '0') AS "Rujukan",
  JIKA(Alter_priv = 'Y', '1', '0') AS "Ubah",
  IF(Drop_priv = 'Y', '1', '0') AS "Drop"
 DARI
    mysql.db
 PERINTAH OLEH
    PENGGUNA, Db;

Sebarang bantuan amat kami hargai. Terima kasih terlebih dahulu!

Answer 1

Berikut ialah contoh "nyahpivot":

(SELECT User AS Users, 'Select' AS Privileges, 1 AS PrivOrder, MAX(CASE db WHEN 'performance_schema' THEN Select_priv='Y' END) AS `performance_schema`, MAX(CASE db WHEN 'sys' THEN Select_priv='Y' END) AS `sys` FROM db GROUP BY User)
UNION
(SELECT User, 'Insert', 2, MAX(CASE db WHEN 'performance_schema' THEN Insert_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Insert_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Update', 3, MAX(CASE db WHEN 'performance_schema' THEN Update_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Update_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Delete', 4, MAX(CASE db WHEN 'performance_schema' THEN Delete_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Delete_priv='Y' END) FROM db GROUP BY User)
UNION
(SELECT User, 'Create', 5, MAX(CASE db WHEN 'performance_schema' THEN Create_priv='Y' END), MAX(CASE db WHEN 'sys' THEN Create_priv='Y' END) FROM db GROUP BY User)
...
ORDER BY Users, PrivOrder;

Maaf, tiada cara untuk mentakrifkan pertanyaan pangsi atau nyahpaut secara automatik untuk lajur setiap skema berdasarkan data yang ditemui oleh pertanyaan. Lajur mesti ditetapkan dalam pertanyaan sebelum pertanyaan mula membaca data.