對MySQL表進行解構操作

Question

我想知道是否可以將這個輸出更改為：

User Database Select Insert Update Delete Create References 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

變成這樣：

Users Privileges performance_schema sys
----- ---------- ------------------ ---
mysql.session Select 1
mysql.session Insert 0
mysql.session Update 0
mysql.session Delete 0
mysql.session Create 0
mysql.session References 0
mysql.session Alter 0
mysql.session Drop 0
mysql.sys Select 0
mysql.sys Insert 0
mysql.sys Update 0
mysql.sys Delete 0
mysql.sys Create 0
mysql.sys References 0
mysql.sys Alter 0
mysql.sys Drop 0

我使用的查詢語句是：

SELECT
  DISTINCT
  USER "User",
  db "Database",
  IF(Select_priv = 'Y', '1 ', '0') AS "Select",
  IF(Insert_priv = 'Y', '1 ', '0') AS "Insert",
  IF(Update_priv = 'Y', '1', '0') AS "Update",
  IF(Delete_priv = 'Y', '1', '0') AS "Delete",
  IF(Create_priv = 'Y', '1', '0') AS "Create",
  IF(References_priv = 'Y', '1', '0') AS "References",
  IF(Alter_priv = 'Y', '1', '0') AS "Alter",
  IF(Drop_priv = 'Y', '1', '0') AS "Drop"
 FROM
    mysql.db
 ORDER BY
    USER, Db;

任何幫助將不勝感激。提前謝謝！

Answer 1

這是一個「unpivot」的範例：

(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;

抱歉，無法自動根據查詢找到的資料為每個模式定義列的透視或反透視查詢。在查詢開始讀取資料之前，必須在查詢中設定列。