移调多个列到一行的MySQL
问题描述:
create table tab_1(
t1 decimal (10),
t2 decimal (10),
t3 decimal (10)
);
insert into tab_1(t1, t2, t3)
values(1,-2,-5);
insert into tab_1(t1, t2, t3)
values(-3,4,6);
insert into tab_1(t1, t2, t3)
values(5,1,2);
select @sum1:=sum(t1) FROM tab_1 WHERE t1 > 0
select @sum2:=sum(t2) FROM tab_1 WHERE t2 > 0
select @sum3:=sum(t3) FROM tab_1 WHERE t3 > 0
select @sum1, @sum2, @sum3;
@sum1 @sum2 @sum3
------------------
6 5 8
我有上述查询和我想要的转置的列在一列和3行,如下面的查询:
Sum
--
6
5
8
我使用MySQL Workbench 6.3.7。
答
您可以通过UNION ALL
SELECT SUM(IF(t1 > 0 , t1, 0)) AS sum FROM tab_1
UNION ALL
SELECT SUM(IF(t2 > 0 , t2, 0)) AS sum FROM tab_1
UNION ALL
SELECT SUM(IF(t3 > 0 , t3, 0)) AS sum FROM tab_1
编辑:
为了储存变量的结果: confused.why你需要吗?
SET @sum1 := 0;
SET @sum2 := 0;
SET @sum3 := 0;
SELECT @sum1 := SUM(IF(t1 > 0 , t1, 0)) AS sum FROM tab_1
UNION ALL
SELECT @sum2:= SUM(IF(t2 > 0 , t2, 0)) AS sum FROM tab_1
UNION ALL
SELECT @sum3 := SUM(IF(t3 > 0 , t3, 0)) AS sum FROM tab_1;
SELECT @sum1,@sum2,@sum3;
答
您几乎在那里与您的变量分配。只要把它们之间union all
:
select 't1', sum(t1) FROM tab_1 WHERE t1 > 0
union all
select 't2', sum(t2) FROM tab_1 WHERE t2 > 0
union all
select 't3', sum(t3) FROM tab_1 WHERE t3 > 0;
(我删除的变量赋值,因为似乎没有必要。)
我将包括一个标识符,我知道哪个值对应于列。
您正在寻找SQL Server中的“UNPIVOT”功能。不幸的是,MySQL确实具备这种功能,并且我被告知它变得非常难看,非常快。对于你给出的简单例子,你可以将'UNION'放在一起。 –