SQL练习-行转列

SQL执行顺序

from --> join --> on --> where --> group by ---> select后面的普通字段，聚合函数count,sum ---> having ---> distinct ---> order by ---> limit

行转列

行转列的常规做法，group by + sum(if())【或count(if())】

例1

year	month	amount
1991	1	1.1
1991	2	1.2
1991	3	1.3
1991	4	1.4
1992	1	2.1
1992	2	2.2
1992	3	2.3
1992	4	2.4

查成这样一个效果

year	m1	m2	m3	m4
1991	1.1	1.2	1.3	1.4
1992	2.1	2.2	2.3	2.4

# if(condition,true,false)
select year,
	   sum(if(month='1',amount,0)) as m1,
	   sum(if(month='1',amount,0)) as m2,
	   sum(if(month='1',amount,0)) as m3,
	   sum(if(month='1',amount,0)) as m4 
from table1 group by year

# case field when value then true else false end
select year,
	   sum(CASE month WHEN '1' THEN amount ELSE '0' END) as m1,
	   sum(CASE month WHEN '2' THEN amount ELSE '0' END) as m2,
	   sum(CASE month WHEN '3' THEN amount ELSE '0' END) as m3,
	   sum(CASE month WHEN '4' THEN amount ELSE '0' END) as m4 
from table1 group by year

例2

有表A，结构如下：

其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。
请用SQL语句实现将上表中的数据合并，合并后的数据为：

其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。
如果该产品在某仓库中无库存量，那么就是0代替。

# if(condition,true,false)
select pid,
       sum(if(sid=1,pnum,0)) as s1_num,
	   sum(if(sid=2,pnum,0)) as s2_num,
	   sum(if(sid=3,pnum,0)) as s3_num
from table2 group by pid

# case field when value then true else false end
select pid,
       sum(CASE sid WHEN 1 THEN pnum ELSE 0 END) as s1_num,
       sum(CASE sid WHEN 2 THEN pnum ELSE 0 END) as s2_num,
	   sum(CASE sid WHEN 3 THEN pnum ELSE 0 END) as s3_num
from table2 group by pid

例3

统计每日胜负结果

# if(condition,true,false)
select ddate,
       count(if(dresult='胜',1,null)) as '胜',
       count(if(dresult='负',1,null)) as '负'
from table3 group by ddate;

# case field when value then true else false end
select ddate,
       count(CASE dresult WHEN '胜' THEN 1 ELSE null END) as '胜',
       count(CASE dresult WHEN '负' THEN 1 ELSE null END) as '负'
from table3 group by ddate;

#注意：这里count里case-when的部分，不满足的情况else时不能简单的用0，需要是null，因为count(0)也会统计为一条记录。【使用sum的时候可以给0。】