我有一个SQL查询问题。我当前的表如下所示:
+--------+------+-----+-----+------+
| hostid | itemname | itemvalue |
+--------+------+-----+-----+------+
| A | 1 | 10 |
+--------+------+-----+-----+------+
| B | 2 | 3 |
+--------+------+-----+-----+------+
我将如何编写查询以便获得这样的输出?
+--------+--
| A | B |
+--------+--
| 1 | 2 |
+--------+--
| 10 | 3 |
+--------+--
最佳答案
通常,您要寻找的东西称为“枢轴”。但是,这需要一个小的“技巧”-使用cross join
-允许将2列移动到2行中。然后,使用max()
和group by
生成枢轴。
SQL Fiddle
MySQL 5.6模式设置:
CREATE TABLE Table1
(`hostid` varchar(1), `itemname` int, `itemvalue` int)
;
INSERT INTO Table1
(`hostid`, `itemname`, `itemvalue`)
VALUES
('A', 1, 10),
('B', 2, 3)
;
查询1:
select a, b
from (
select
max(case
when n = 1 and hostid = 'A' then itemname
when n = 2 and hostid = 'A' then Itemvalue
end) A
, max(case
when n = 1 and hostid = 'B' then itemname
when n = 2 and hostid = 'B' then Itemvalue
end) b
, n
from table1
cross join (select 1 n union all select 2) n
group by n
) d
;
Results:
| a | b |
|----|---|
| 1 | 2 |
| 10 | 3 |
关于mysql - SQL将行转列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46997061/