SQL分组统计

La5DotNet 2021-08-02 原文

第一种情况：横向显示

先看下面的一个数据表：

现在的需求是：统计出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 "分段统计（元）"