groupby rollup

最新推荐文章于 2023-03-09 12:19:55 发布
最新推荐文章于 2023-03-09 12:19:55 发布
阅读量673 收藏
点赞数
文章标签： date null c
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/matrixzero/article/details/2685400
版权
select
  (
   case UnitFlg.Sexcd when 1 then '男性'
                        when 2 then '女性' 
                        else '総計' end
   ) sex,
--  UnitFlg.bSexcd,
  (case UnitFlg.agescope when 0 then '30～39'
                           when 1 then  '40～49'
                           when 2 then '50～59'
                           when 3 then '60～69' end) AgeS,
--  UnitFlg.agescope,
  (case UnitFlg.Docshuruino||UnitFlg.Jushinkenumuflg
                           when '11' then '人間ドック'
                           when '12' then '人間ドック・券'
                           when '19' then '人間ドック計'
                           when '21' then '脳ドック'
                           when '22' then '脳ドック・券'
                           when '29' then '脳ドック計'
                           when '31' then '人間ドック・脳'
                           when '32' then '人間ドック・脳・券'
                           when '39' then '人間ドック・脳計'
                           else '合計'
                           end) shurui,
        
        --  UnitFlg.Docshuruino,
        --  UnitFlg.Jushinkenumuflg,
          nvl(detail.Year,0) Year,
          nvl(detail.Jua,0) Jua,
          nvl(detail.Feb,0) Feb,
          nvl(detail.Mar,0) Mar,
          nvl(detail.Apr,0) Apr,
          nvl(detail.May,0) May,
          nvl(detail.Jun,0) Jun,
          nvl(detail.Jul,0) Jul,
          nvl(detail.Aug,0) Aug,
          nvl(detail.Sep,0) Sep,
          nvl(detail.Oct,0) Oct,
          nvl(detail.Nve,0) Nve,
          nvl(detail.Dec,0) Dec
from
---------------------------------------------------------------Header----------------------------------------------------------------------------------------------
        (Select
                nvl(sexcd,3) sexcd,--集計
                agescope,--年代
                nvl(Docshuruino,9) Docshuruino,--種類
                nvl(Jushinkenumuflg,9) Jushinkenumuflg--種類
        From
          ( Select 1 sexcd From dual Union Select 2 From dual) sexcd,
          ( Select 0 ageScope From dual Union Select 1 From dual Union Select 2 From dual Union
            Select 3 From dual) agescope,
          ( Select 1 Docshuruino From dual Union Select 2 From dual Union Select 3 From dual) Docshuruino,
          (Select 1 Jushinkenumuflg From dual Union Select 2 From dual ) Jushinkenumuflg
        --   Group By Grouping Sets((sexcd,agescope,Docshuruino,Jushinkenumuflg),(sexcd,agescope,Docshuruino),(sexcd,agescope))
          Group by rollup(sexcd),ageScope,rollup(Docshuruino,Jushinkenumuflg)
        Order By sexcd,agescope,Docshuruino,Jushinkenumuflg) UnitFlg,
--------------------------------------------------------------Detail-------------------------------------------------------------------------------------------------
        (select
               nvl(x.Sexcd,3) sexcd,--集計
               x.agescope,--年代
               nvl(x.Docshuruino,9) Docshuruino,--種類
               nvl(x.Jushinkenumuflg,9) Jushinkenumuflg,--種類
              count(x.months) Year, --合計
              count(case when x.months='1' then x.months end) Jua,--１月
              count(case when x.months='2' then x.months end) Feb,--２月
              count(case when x.months='3' then x.months end) Mar,--３月
              count(case when x.months='4' then x.months end) Apr,--４月
              count(case when x.months='5' then x.months end) May,--５月
              count(case when x.months='6' then x.months end) Jun,--６月
              count(case when x.months='7' then x.months end) Jul,--７月
              count(case when x.months='8' then x.months end) Aug,--８月
              count(case when x.months='9' then x.months end) Sep,--９月
              count(case when x.months='10' then x.months end) Oct,--１０月
              count(case when x.months='11' then x.months end) Nve,--１１月
              count(case when x.months='12' then x.months end) Dec--１２月
        from
           -------------------------------------------------PreHandle_Start----------------------------------------------------------------------------
                    (select Kojinno,
                           Docshuruino,
                           Jushinkenumuflg,
                           Sexcd,
                           Age,
                          (
                             case
                                 when Age between 30 and 39 then 0
                                 when Age between 40 and 49 then 1
                                 when Age between 50 and 59 then 2
                                 when Age between 60 and 69 then 3
                             end
                           ) AgeScope,
                           Jushinorderdate,
                           Months
                     from (
                              Select A.Kojinno,
                                     A.Docshuruino,
                                     A.Jushinkenumuflg,
                                     B.Sexcd,
                                    TRUNC(MONTHS_BETWEEN(DECODE(c.Nenreicalckbn,
                                             2,TO_DATE(TO_CHAR(TO_NUMBER(a.Nendo) + 1) || '0331'),
                                             3,TO_DATE(TO_CHAR(TO_NUMBER(a.Nendo) + 1) || '0401'),
                                             Sysdate),
                                             b.BirthDay)/12, 0
                                    ) Age,
                                    to_char(a.Jushinorderdate,'yyyymmdd') Jushinorderdate,
                                    (
                                     case
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'0401','yyyymmdd') and to_date(to_char(a.nendo)||'0430','yyyymmdd') then '4'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'0501','yyyymmdd') and to_date(to_char(a.nendo)||'0531','yyyymmdd') then '5'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'0601','yyyymmdd') and to_date(to_char(a.nendo)||'0630','yyyymmdd') then '6'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'0701','yyyymmdd') and to_date(to_char(a.nendo)||'0731','yyyymmdd') then '7'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'0801','yyyymmdd') and to_date(to_char(a.nendo)||'0831','yyyymmdd') then '8'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'0901','yyyymmdd') and to_date(to_char(a.nendo)||'0930','yyyymmdd') then '9'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'1001','yyyymmdd') and to_date(to_char(a.nendo)||'1031','yyyymmdd') then '10'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'1101','yyyymmdd') and to_date(to_char(a.nendo)||'1130','yyyymmdd') then '11'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo)||'1201','yyyymmdd') and to_date(to_char(a.nendo)||'1231','yyyymmdd') then '12'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo+1)||'0101','yyyymmdd') and to_date(to_char(a.nendo+1)||'0131','yyyymmdd') then '1'
                                         when to_char(a.Jushinorderdate,'yyyymmdd')>=(a.nendo+1)||'0201' and to_char(a.Jushinorderdate,'yyyymmdd')<=(a.nendo+1)||'0229' then '2'
                                         when a.Jushinorderdate between to_date(to_char(a.nendo+1)||'0301','yyyymmdd') and to_date(to_char(a.nendo+1)||'0331','yyyymmdd') then '3'
                                     end
                                    ) Months
                                from  kwdocinfo a ,
                                       kojininfo b,
                                       (Select NenreiCalcKbn From SystemConfig Where SystemConfigID = 1) c
                                where A.Nendo=2008 And
                                       A.Delflg=1 And
                                       A.Kojinno=B.Kojinno And
                                       B.Birthday Is Not Null And
                                       B.SEXCD is not null and
                                       A.Jushinorderdate Is Not Null)
                     Where Months Is Not Null And
                             age between 30 and 69 and
                             sexcd between 1 and 2 and
                             Docshuruino between 1 and 3 and
                             Jushinkenumuflg between 1 and 2
                    ) X
        -------------------------------------------------------------PreHandle_End------------------------------------------------------------------------------------
        --Group By Grouping Sets((sexcd,agescope,Docshuruino,Jushinkenumuflg),(sexcd,agescope,Docshuruino),(sexcd,agescope))
        Group by rollup(sexcd),ageScope,rollup(Docshuruino,Jushinkenumuflg)
        ) detail
where UnitFlg.sexcd=detail.sexcd(+) and
       unitflg.ageScope=detail.ageScope(+) and
       unitflg.Docshuruino=detail.Docshuruino(+) and
       unitflg.Jushinkenumuflg=detail.Jushinkenumuflg(+)
matrixzero
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
group by后使用rollup子句总结
10-14
group by后使用rollup子句总结，oracle开发人员值得一看
【转】group by后使用rollup子句总结
weixin_34168700的博客
05-29 935
今天接触到了group by后带rollup子句的用法（真是丑死人了，接触ORACLE一两年了今天才知道还有这样一个用法），group by后带rollup子句这个子句所产生的功能实在是太高级了，见了就高兴。但完后感觉也没什么的，很简单的。 下面按我的理解简单总结一下： 一、如何理解group by后带rollup子句所产生的效果 group by后带rollup子句的功能可以理解为：先按一...
group by rollup(列名)
qq_43118674的博客
05-30 544
group by rollup(列名)：可对列为空值和非空值的记录，进行汇总。 group by 列名：只能对列非空的记录，进行汇总。
Group by后使用rollup
Baron_ND的博客
08-26 5051
一、如何理解group by后带rollup子句所产生的效果 group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。因此要搞懂group by后带rollup子句的用法主要是搞懂它是如何按一定的规则产生多种分组的。另group by后带rollup子句所返回的结果集，可以理解为各个分组所产生的结果集的并集且没有去掉重复数据。下面举例说明： 1、对比没有带rollup的go
GROUP BY ROLLUP和CUBE 用法
aptsvtb7338的博客
12-31 251
ROLLUP和CUBE 用法 Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。 如果是Group by ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。 如果...
group by 后 使用 rollup 子句 总结.doc
10-27
### Group By 后使用 Rollup 子句的理解与应用 #### 一、Rollup 子句的基本概念 在 SQL 查询语言中，`GROUP BY` 语句被广泛应用于对数据进行分组处理，以便能够更好地汇总和分析数据。而在 `GROUP BY` 语句后加上 ...
处理group by 查询速度太慢的问题 数据量大.doc
12-18
优化 Group By 查询速度的实践经验 在实际项目中，遇到了表数据量大导致查询速度很慢的问题。通过记录和优化过程，总结出一些有价值的经验，希望能够帮助读者解决类似的问题。 知识点1：Group By 查询的索引设置 ...
Oracle中分组查询group by用法规则详解
12-14
- `GROUP BY GROUPING SETS`是`ROLLUP`和`CUBE`的组合，可自定义分组。 了解这些基本规则和用法后，你可以根据业务需求灵活运用`GROUP BY`来分析和汇总数据。记得在实际操作中，确保遵守上述规则，以避免SQL语法...
Sequelize中用group by进行分组聚合查询
12-23
这里，`GROUP BY`后的列名用于确定分组，`HAVING`用于在分组后对结果进行过滤，`WITH ROLLUP`则用于添加额外的汇总行。常见的聚合函数有： - `COUNT()`：统计记录条数 - `SUM()`：计算字段的值的总和 - `AVG()`...
mysql groupby rullup_深入讲解如何使用rollup配合groupby命令
weixin_33897367的博客
01-26 131
Oracle数据库中的rollup配合group by命令使用，可以提供信息汇总功能(与"小计"相似)示例如下：SQL> select job,deptno,sal from emp;JOB DEPTNO SAL--------- --------- ---------CLERK 20 800SALESMAN 30 1600SALESMAN 30 1250MANAGER 20 2975SAL...
SQL语句中Group BY 和Rollup以及cube用法
08-18
SQL语句中Group BY 和Rollup以及cube用法
【SQL】group by rollup的使用
西瓜那么甜
08-12 2万+
这周看代码，看到了一个sql语法，使用的是group by rollup，一开始看文档没有明白，后来写了个例子又问了问别人才明白。不多说直接上例子，因为是例子所以就不要纠结字段类型了。 首先创建一张表  CREATE TABLE "user" ( "id" int(11) NOT NULL AUTO_INCREMENT, "name" varchar(20) DEFAULT NULL...
一文看懂hive中group by rollup/cube group by sets用法及区别（附案例）
Yuki的博客
11-17 5765
一文看懂group by rollup/cube group by sets用法及区别（附案例）
mysql 过滤分页_Mybatis拦截器之数据权限过滤与分页集成
weixin_39997695的博客
02-02 404
解决方案之改SQL原sqlSELECTa.id AS "id",a.NAME AS "name",a.sex_cd AS "sexCd",a.org_id AS "orgId",a.STATUS AS "status",a.create_org_id AS "createOrgId"FROMpty_person aWHEREa. STATUS = 0org_id是单位的标识，也就是where条件里...
group by rollup函数的用法
java框架技术
07-28 1971
[code="java"] 有个表a有字段c1,c2有记录 2015-03-01 2 2015-03-01 3 2015-03-02 5 想要得到得到如下结果 2015-03-01 4 2015-03-02 5 合计 9 可以通过 select decode(grouping(c1),1,'合计',c1),sum(c2) from ...
group by rollup与group by cube，你了解吗？
Mark
10-04 8074
一、分组依据为一列时 1、使用group by分组查询 select exam_no,avg (result) as 平均分 from result_info group by exam_no 查询结果如下： 2、使用group by rollup 和group by cube的结果 select exam_no,avg(result) as 平均分 from result...
MySQL分类汇总(group by...with rollup)统计时，如何显示“总计”字段?
热门推荐
花北城的博客
03-09 3万+
使用grouping这个函数发现一直报错提示无法识别，仔细观察一下原来这是sql的语法，难道MySQL就实现不了了吗？没办法只得自己想办法了，我首先想到的方法是使用带条件的子查询，输出到最后一条数据时显示“总计”字段，但又嫌太复杂没有写。=”、“>”、“>=”、“
decode函数_超详细的5个Oracle数据库分组函数总结
weixin_39942488的博客
11-27 486
概述今天主要分享下Oracle数据库分组函数group by 、 rollup、cude、grouping 、grouping sets的常用用法，以下以Oracle自带schema做演示。1、group by的使用--根据DEPTNO和JOB进行分组。求相同DEPTNO,相同JOB的员工工资总和。SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP ...
group by rollup
最新发布
08-17
GROUP BY ROLLUP is a clause used in SQL to generate multiple levels of subtotals in the result set. It is often used in conjunction with the GROUP BY clause to produce a hierarchical breakdown of data. When you use GROUP BY ROLLUP, the result set will include additional rows that represent different levels of aggregation. These rows will contain NULL values in the columns that represent higher-level aggregations. For example, consider a table "Sales" with columns "Region", "Country", and "Product". If you want to get subtotals at the region, country, and product levels, you can use the GROUP BY ROLLUP clause like this: ``` SELECT Region, Country, Product, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY ROLLUP (Region, Country, Product); ``` The result set will include rows for each combination of region, country, and product, as well as subtotals for each level of aggregation. The rows representing higher-level aggregations will have NULL values in the corresponding columns. Note that the number of rows returned can be significantly larger when using GROUP BY ROLLUP compared to a simple GROUP BY clause. Therefore, it's important to consider the performance implications when using this feature.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值