sql server 按照区间进行分组的两种语法
一、按照区间进行分组
select CASE WHEN a.Commission>0 and a.Commission<10 THEN '[0-10]' WHEN a.Commission>10 and a.Commission<20 THEN '[10-20]' WHEN a.Commission>20 and a.Commission<30 THEN '[20-30]' WHEN a.Commission>30 and a.Commission<50 THEN '[30-50]' ELSE '[50]' END as 'Price',sum(a.ClickNum) ClickNum,count(b.productId) AskNum,sum(if(OrderState=11,1,0)) FinishNum,Round(sum(if(OrderState=11,1,0))/if(count(b.productId)=0,1,count(b.productId)),2)*100 TestFinishNum,Round(sum(if(ConmmentSate=2,1,0))/if(count(b.productId)=0,1,count(b.productId)),2)*100 CommentChance from fksd_product a left join fksd_commentorder b on a.Id=b.ProductId where a.IsDelete!=10 GROUP BY CASE WHEN a.Commission>0 and a.Commission<10 THEN '[0-10]' WHEN a.Commission>10 and a.Commission<20 THEN '[10-20]' WHEN a.Commission>20 and a.Commission<30 THEN '[20-30]' WHEN a.Commission>30 and a.Commission<50 THEN '[30-50]' ELSE '[50]' END ;
二、第二种比较蠢的方式,具体实现如下:
select 0-3" f1,cnt 数量 from ( select count(*) cnt from 表 where 字段>=0 and 字段<=3 ) a union all select "4-6" f1,cnt 数量 from ( select count(*) cnt from 表 where 字段>=4 and 字段<=6 )b union all select "7-10" f1,cnt 数量 from ( select count(*) cnt from 表 where 字段>=7 and 字段<=10 ) c