SQL分组统计

第一种情况：横向显示

先看下面的一个数据表：

现在的需求是：统计出v_coun每一种取值情况下，对就的V_iseneded的数量。

Oracle中的 SQL代码如下：

select v_count,sum(case when v_isended=1 then 1 else 0 end) as 一的数量,
sum(case when v_isended=0 then 1 else 0 end) as 零的数量
from t_vote 
group by v_count

结果如下：

 第二种情况：竖向显示

表结构如下：

对应的列名意思为：员工工号、员工姓名、计件工资。上面只是一部分数据，总共的数据有七百多条

现在的需求是：对这些员工的计件工资进行分段统计，结果如下图：

Oracle中SQL代码如下所示：

select "分段统计（元）", count("分段统计（元）") as "本月（人）"
  from (select 
               (case when pay_marketcount < 100 then cast('100以下' as varchar(10))
                 else(case when pay_marketcount <= 500 then cast('100-500' as varchar(10))
                 else (case when pay_marketcount <= 1000 then cast('501-1000' as varchar(10))
                 else (case when pay_marketcount <= 1500 then cast('1001-1500' as varchar(10))
                 else (case when pay_marketcount <= 2000 then cast('1501-2000' as varchar(10))
                 else (case when pay_marketcount <= 2500 then cast('2001-2500' as varchar(10))
                 else (case when pay_marketcount <= 3000 then cast('2501-3000' as varchar(10))
                 else (case when pay_marketcount <= 4000 then cast('3001-4000' as varchar(10))
                 else (case when pay_marketcount <= 5000 then cast('4001-5000' as varchar(10))
                 else (case when pay_marketcount > 5000 then cast('5000以上' as varchar(10))
               end) end) end) end) end) end) end) end) end) end) as "分段统计（元）"
          from t_payment)
 group by "分段统计（元）"

 

转载于:https://www.cnblogs.com/La5DotNet/archive/2012/11/23/2784008.html