今天一个同学Q发来一个问题给我:
表结构很简单,问题也很短,但是我分析了很长时间(我对sql的了解仅限于一些基础)
一开始分析感觉要按照产品,颜色分组,然后弄出了类似
select productname,color,sum(num) from test.table2 group by productname,color
然后开始在having字句上做文章,最后各种搞不定。
后面偶然看到了 case when ... then ... else ... end 这样的语法
就构造这样的语句实现了第一问
select
productname,
sum(num*(case when (color='hong') then 1 else -1 end)) as maxnum from test.table2 group by productname
having sum(num*(case when (color='hong') then 1 else -1 end))>0
然后第二问也迎刃而解了:
select
productname,
sum(num*(case when (color='hong') then 1 else 0 end)) as hongnum,
sum(num*(case when (color='lan') then 1 else 0 end)) as lannum
from test.table2
group by productname;