MySQL行轉(zhuǎn)列的實現(xiàn)方式有以下幾種:
- 使用CASE語句:通過使用CASE語句可以根據(jù)條件將行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)。
SELECT
id,
MAX(CASE WHEN attribute = 'attribute1' THEN value END) AS attribute1,
MAX(CASE WHEN attribute = 'attribute2' THEN value END) AS attribute2,
MAX(CASE WHEN attribute = 'attribute3' THEN value END) AS attribute3
FROM
table
GROUP BY
id;
- 使用PIVOT函數(shù):MySQL并沒有內(nèi)置的PIVOT函數(shù),但是可以使用子查詢和聚合函數(shù)來模擬實現(xiàn)。
SELECT
id,
MAX(CASE WHEN attribute = 'attribute1' THEN value END) AS attribute1,
MAX(CASE WHEN attribute = 'attribute2' THEN value END) AS attribute2,
MAX(CASE WHEN attribute = 'attribute3' THEN value END) AS attribute3
FROM
(SELECT id, attribute, value FROM table) AS t
GROUP BY
id;
- 使用GROUP_CONCAT函數(shù):可以使用GROUP_CONCAT函數(shù)將多個行數(shù)據(jù)合并為一個字符串,然后再使用子查詢將字符串拆分為多個列。
SELECT
id,
SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN attribute = 'attribute1' THEN value END), ',', 1) AS attribute1,
SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN attribute = 'attribute2' THEN value END), ',', 1) AS attribute2,
SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN attribute = 'attribute3' THEN value END), ',', 1) AS attribute3
FROM
table
GROUP BY
id;
以上是幾種常見的MySQL行轉(zhuǎn)列的實現(xiàn)方式,可以根據(jù)實際需求選擇適合的方式。