关于sql实现二维报表的方法

         我们在sql编写中经常会碰到一些二维数表的情况，一个二维数表中每一个数据都有至少两个条件的约束。

         如下图中工人的数据数据，我们规定id以10,20开头的为车间一的工人，30,40开头的为车间二的工人，50,60开头的为车间三的工人，70,80开头的为车间四的工人，90,x1开头的为车间五的工人。Worktime为工作年限。

我们根据上面的数据求如下二维表

如果我们单独查每个框中的人数，是非常简单实现的。只需要加上工作年限在1-3年以及车间一这两个条件就行。完成方式如下：

select count(*) from workers where substr(id,1,2) in ('10','20') and worktime<3;

我们上面报表为5*5的报表，如果我们分别查每个单元的值如果我们按照上述方式一个格一个格的查，我们就要修改25次条件，这样显然是很费力的。而且我们以后很可能面临10*10甚至更多的单元格。

我们可以通过如下几种方式来解决二维数表的问题。

方法一：通过sql以及excl的数据透视表的方式。

我们可以按照车间和年限分组，sql如下：

select

         case

           when substr(id,1,2) in ('10','20') then '车间一'

                   when substr(id,1,2) in ('30','40') then '车间二'

                   when substr(id,1,2) in ('50','60') then '车间三'

                   when substr(id,1,2) in ('70','80') then '车间四'

                   when substr(id,1,2) in ('90','x0') then '车间五'

  end '车间',

  case

          when worktime<3 then '1-3年'

          when worktime>=3 and worktime<5 then '3-5年'

          when worktime>=5 and worktime<7 then '5-7年'

          when worktime>=7 and worktime<9 then '7-9年'

          when worktime>=9 then '9年以上'

 end '年限',

 count(*) '人数'      

 from workers

 group by

        case

           when substr(id,1,2) in ('10','20') then '车间一'

                   when substr(id,1,2) in ('30','40') then '车间二'

                   when substr(id,1,2) in ('50','60') then '车间三'

                   when substr(id,1,2) in ('70','80') then '车间四'

                   when substr(id,1,2) in ('90','x0') then '车间五'

  end,

           case

          when worktime<3 then '1-3年'

          when worktime>=3 and worktime<5 then '3-5年'

          when worktime>=5 and worktime<7 then '5-7年'

          when worktime>=7 and worktime<9 then '7-9年'

          when worktime>=9 then '9年以上'

 end

这样我们就得到了每个单元格相关联的两个条件的所有数据：

然后我们把数据粘贴到excl中，通过数据透视表的方式进行处理：

首先我们选中数据范围，点击插入，选择数据透视表，点击确定。

我们把年限放到x轴，车间放到y轴，人数放入数据中，我们即可得到二维报表。

方法二：通过sql实现

我们的逻辑为把年限通过行列转换的方式，然后通过纵轴的车间进行分组进行加和。

Sql如下：

select     

         case

           when substr(id,1,2) in ('10','20') then '车间一'

                   when substr(id,1,2) in ('30','40') then '车间二'

                   when substr(id,1,2) in ('50','60') then '车间三'

                   when substr(id,1,2) in ('70','80') then '车间四'

                   when substr(id,1,2) in ('90','x0') then '车间五'

  end '车间',

         sum(case when worktime<3 then 1 else 0 end ) '1-3年',

         sum(case when worktime>=3 and worktime<5 then 1 else 0 end ) '3-5年',

         sum(case when worktime>=5 and worktime<7 then 1 else 0 end ) '5-7年',

         sum(case when worktime>=7 and worktime<9 then 1 else 0 end ) '7-9年',

         sum(case when worktime>=9  then 1 else 0 end ) '9年以上'

         from workers

group by

         case

           when substr(id,1,2) in ('10','20') then '车间一'

                   when substr(id,1,2) in ('30','40') then '车间二'

                   when substr(id,1,2) in ('50','60') then '车间三'

                   when substr(id,1,2) in ('70','80') then '车间四'

                   when substr(id,1,2) in ('90','x0') then '车间五'

  end

         ORDER BY

                   case

           when substr(id,1,2) in ('10','20') then '车间一'

                   when substr(id,1,2) in ('30','40') then '车间二'

                   when substr(id,1,2) in ('50','60') then '车间三'

                   when substr(id,1,2) in ('70','80') then '车间四'

                   when substr(id,1,2) in ('90','x0') then '车间五'

  end

        

运行结果如下：