db2 select中使用case替代行转列操作 .

最新推荐文章于 2023-11-12 22:33:01 发布

zw7534313

最新推荐文章于 2023-11-12 22:33:01 发布

阅读量305

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/zw7534313/article/details/84007250

版权

sql 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

在DB2中进行行转列比较麻烦，这里我在select里用case语法代替行转列操作

使用以下查询语句可以实现

select t.CHECK_UNIT as 公司,
       t.BOE_DEPT_ID as 部门,
       sum(t.total) as 单据总数,
       sum(t.seven) as 星期天,
       sum(t.one) as 星期一,
       sum(t.two) as 星期二,
       sum(t.three) as 星期三,
       sum(t.four) as 星期四,
       sum(t.five) as 星期五,
       sum(t.six) as 星期六
from (select bh.CHECK_UNIT,
               bh.BOE_DEPT_ID,
               count(*) as total,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 1) then
                        1
                     end) as seven,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 2) then
                        1
                     end) as one,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 3) then
                        1
                     end) as two,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 4) then
                        1
                     end) as three,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 5) then
                        1
                     end) as four,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 6) then
                        1
                     end) as five,
               count(case
                       when (DAYOFWEEK(bh.BOE_DATE) = 7) then
                        1
                     end) as six
          from sie.SIE_BOE_HEADERS bh
         where bh.BOE_DATE > TIMESTAMP('2007-7-20-12.00.00.000000')
         group by bh.CHECK_UNIT, bh.BOE_DEPT_ID, DAYOFWEEK(bh.BOE_DATE)) t
group by t.CHECK_UNIT, t.BOE_DEPT_ID;