MySQL - 行到列我試圖搜索帖子,但我只找到了SQL Server / Access的解決方案。我需要一個MySQL(5.X)的解決方案。我有一個表(稱為歷史)有3列:hostid,itemname,itemvalue。如果我執行select(select * from history),它將返回 +--------+----------+-----------+ | hostid | itemname | itemvalue | +--------+----------+-----------+ | 1 | A | 10 | +--------+----------+-----------+ | 1 | B | 3 | +--------+----------+-----------+ | 2 | A | 9 | +--------+----------+-----------+ | 2 | c | 40 | +--------+----------+-----------+如何查詢數據庫以返回類似的內容 +--------+------+-----+-----+ | hostid | A | B | C | +--------+------+-----+-----+ | 1 | 10 | 3 | 0 | +--------+------+-----+-----+ | 2 | 9 | 0 | 40 | +--------+------+-----+-----+
3 回答

慕田峪9158850
TA貢獻1794條經驗 獲得超8個贊
SELECT hostid, sum( if( itemname = 'A', itemvalue, 0 ) ) AS A, sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, sum( if( itemname = 'C', itemvalue, 0 ) ) AS C FROM bob GROUP BY hostid;

蕭十郎
TA貢獻1815條經驗 獲得超13個贊
另一個選項,如果你有很多需要轉動的項目,特別有用的是讓mysql為你構建查詢:
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'ifnull(SUM(case when itemname = ''', itemname, ''' then itemvalue end),0) AS `', itemname, '`' ) ) INTO @sqlFROM history;SET @sql = CONCAT('SELECT hostid, ', @sql, ' FROM history GROUP BY hostid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
FIDDLE 添加了一些額外的值以使其正常工作
GROUP_CONCAT
默認值為1000,因此如果您有一個非常大的查詢,請在運行之前更改此參數
SET SESSION group_concat_max_len = 1000000;
測試:
DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);
INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
(2,'C',40),(2,'D',5),
(3,'A',14),(3,'B',67),(3,'D',8);
hostid A B C D
1 10 3 0 0
2 9 0 40 5
3 14 67 0 8
添加回答
舉報
0/150
提交
取消