MySQL - 行到列我試圖搜索帖子，但我只找到了SQL Server / Access的解決方案。我需要一個(gè)MySQL（5.X）的解決方案。我有一個(gè)表（稱(chēng)為歷史）有3列：hostid，itemname，itemvalue。如果我執(zhí)行select（select * from history），它將返回 +--------+----------+-----------+ | hostid | itemname | itemvalue | +--------+----------+-----------+ | 1 | A | 10 | +--------+----------+-----------+ | 1 | B | 3 | +--------+----------+-----------+ | 2 | A | 9 | +--------+----------+-----------+ | 2 | c | 40 | +--------+----------+-----------+如何查詢(xún)數(shù)據(jù)庫(kù)以返回類(lèi)似的內(nèi)容 +--------+------+-----+-----+ | hostid | A | B | C | +--------+------+-----+-----+ | 1 | 10 | 3 | 0 | +--------+------+-----+-----+ | 2 | 9 | 0 | 40 | +--------+------+-----+-----+
3 回答
慕田峪9158850
TA貢獻(xiàn)1794條經(jīng)驗(yàn) 獲得超7個(gè)贊
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貢獻(xiàn)1815條經(jīng)驗(yàn) 獲得超13個(gè)贊
另一個(gè)選項(xiàng)，如果你有很多需要轉(zhuǎn)動(dòng)的項(xiàng)目，特別有用的是讓mysql為你構(gòu)建查詢(xún)：
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 默認(rèn)值為1000，因此如果您有一個(gè)非常大的查詢(xún)，請(qǐng)?jiān)谶\(yùn)行之前更改此參數(shù)
SET SESSION group_concat_max_len = 1000000;
測(cè)試：
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
添加回答
舉報(bào)
0/150
提交
取消