按照 title(产品名称),color(颜色)分组,(按班次(banci)查询统计数量)统计
select c1.title,c1.color,
SUM(case when c1.banci='早' then c1.chanliang else 0 end)as '早' ,
SUM(case when c1.banci='中' then c1.chanliang else 0 end)as '中' ,
SUM(case when c1.banci='晚' then c1.chanliang else 0 end)as '晚'
from goos as c1 group by c1.title,c1.color WITH ROLLUP
GROUP BY WITH ROLLUP
ROLLUP与CUBE作用一样,都是在GROUP BY的基础上,进一步归纳统计数据,但是ROLLUP比CUBE的限制要多。它不会将所有的字段混合归纳,而是根据GROUP BY后面字段的顺序,从高等级字段向低等级字段归纳,因此GROUP BY后面字段的顺序会影响分组结果。
查询结果
去掉 WITH ROLLUP 得到结果
select c1.title,c1.color,
SUM(case when c1.banci='早' then c1.chanliang else 0 end)as '早' ,
SUM(case when c1.banci='中' then c1.chanliang else 0 end)as '中' ,
SUM(case when c1.banci='晚' then c1.chanliang else 0 end)as '晚'
from goos as c1 group by c1.title,c1.color