SQL学习之双向表头报表查询
来源:互联网 发布:李安 知乎 编辑:程序博客网 时间:2024/06/03 06:31
接到银行业务需求,要做一个如图的个人年龄区间存款统计报表:
思路:
可以看到,表头是分纵向和横向的,因此首先想到的是需要进行两次group by,而查询时想要将存款区间列作为纵向的表头,年龄区间作为横向表头这种样式,就要把存款类别和存款区间当成列内的数据来看,并将这两列的字段放在查询的前两位(如SELECT TYPE,BAL_ORANGE....)。
由此可以先将原始数据按照存款的区间和类别进行一次分组:
SELECT TYPE, CASE WHEN BAL < 50000 THEN '5万元以下'WHEN BAL BETWEEN 50001 AND 100000 THEN '5-10万' WHEN BAL BETWEEN 100001 AND 200000 THEN '10-20万' WHEN BAL BETWEEN 200001 AND 300000 THEN '20-30万' WHEN BAL BETWEEN 300001 AND 400000 THEN '30-40万' WHEN BAL BETWEEN 400001 AND 500000 THEN '40-50万' WHEN BAL > 500000 THEN '50万以上'END AS BAL_RANGE,BAL, AGE, COUNT(*) COUNT_BFROM C_CUST_AGE WHERE DEPT_ID = ? AND TYPE = ?GROUP BY TYPE, AGE, BAL
(其中的DEPT_ID可以针对不同部门获得对应的分组记录,TYPE为存款类别,仅作为查询条件。)
查询结果如下:
此时要注意的是COUNT_B这个字段,因为CASE WHEN语句只对BAL(余额)字段进行了分类,并作为BAL_RANGE(余额区间)附加在了原来的表上,实际上只对AGE字段进行了分组,所以此时的count_b其实只是当前年龄当前余额的客户数量,此时我们已经有了TYPE(存款类型),BAL_RANGE(余额区间)两个纵向表头,可以加入横向的的年龄区间表头了:
select type,bal_range,CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c,CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b,CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b) ELSE 0 end age31_40c,CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b*bal) ELSE 0 end age31_40b,CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b) ELSE 0 end age41_50c,CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b*bal) ELSE 0 end age41_50b,CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b) ELSE 0 end age51_60c,CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b*bal) ELSE 0 end age51_60b,CASE WHEN AGE > 60 then sum(count_b) ELSE 0 end age60_c,CASE WHEN AGE > 60 then sum(count_b*bal) ELSE 0 end age60_b,count(count_b) sum_count,sum(count_b*bal) sum_bal from(SELECT TYPE, CASE WHEN BAL < 50000 THEN '5万元以下'WHEN BAL BETWEEN 50001 AND 100000 THEN '5-10万' WHEN BAL BETWEEN 100001 AND 200000 THEN '10-20万' WHEN BAL BETWEEN 200001 AND 300000 THEN '20-30万' WHEN BAL BETWEEN 300001 AND 400000 THEN '30-40万' WHEN BAL BETWEEN 400001 AND 500000 THEN '40-50万' WHEN BAL > 500000 THEN '50万以上'END AS BAL_RANGE,BAL, AGE, COUNT(*) COUNT_BFROM C_CUST_AGE WHERE DEPT_ID = ? AND TYPE = ?GROUP BY TYPE, AGE, BAL) GROUP BY TYPE,bal_range,AGE
查询结果如下:
由于COUNT_B只是针对同一年龄的用户数,所以
CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c
表示年龄小于30岁的所有用户数,即age_30c,但如果AGE不在小于30这列,就用0来表示
CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b
同理表示年龄小于30岁的所有用户总余额
此时查询结果会有大量的0存在,是因为还没有按照BAL_RANGE字段进行分组合并,所以最后再进行一次分组即可,最终SQL代码如下:
SELECT type,bal_range, SUM(age_30c) age_30c, sum(age_30b) age_30b, sum(age31_40c) age31_40c, sum(age31_40b) age31_40b, sum(age41_50c) age41_50c, sum(age41_50b) age41_50b,sum(age51_60c) age51_60c, sum(age51_60b) age51_60b, sum(age60_c) age60_c, sum(age60_b) age60_b,sum(sum_count) sum_count, sum(sum_bal) sum_balFROM(select type,bal_range,CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c,CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b,CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b) ELSE 0 end age31_40c,CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b*bal) ELSE 0 end age31_40b,CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b) ELSE 0 end age41_50c,CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b*bal) ELSE 0 end age41_50b,CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b) ELSE 0 end age51_60c,CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b*bal) ELSE 0 end age51_60b,CASE WHEN AGE > 60 then sum(count_b) ELSE 0 end age60_c,CASE WHEN AGE > 60 then sum(count_b*bal) ELSE 0 end age60_b,count(count_b) sum_count,sum(count_b*bal) sum_bal from(SELECT TYPE, CASE WHEN BAL < 50000 THEN '5万元以下'WHEN BAL BETWEEN 50001 AND 100000 THEN '5-10万' WHEN BAL BETWEEN 100001 AND 200000 THEN '10-20万' WHEN BAL BETWEEN 200001 AND 300000 THEN '20-30万' WHEN BAL BETWEEN 300001 AND 400000 THEN '30-40万' WHEN BAL BETWEEN 400001 AND 500000 THEN '40-50万' WHEN BAL > 500000 THEN '50万以上'END AS BAL_RANGE,BAL, AGE, COUNT(*) COUNT_BFROM C_CUST_AGE WHERE DEPT_ID = ? AND TYPE = ?GROUP BY TYPE, AGE, BAL) GROUP BY TYPE,bal_range,AGE) group by type,bal_range
最终结果集:
博主新手,有更好的方法还请指教。
阅读全文
0 0
- SQL学习之双向表头报表查询
- 生成报表表头SQL示例
- 报表数据查询SQL
- SQL语句之利用UNION查询生成报表
- MyBatis学习(二)之双向一对多关联映射查询
- MyBatis学习(二)之双向一对多关联映射查询
- SQL学习之高级查询
- Hibernate学习之---SQL查询
- SQL 按月份查询报表
- SQL报表 - Tablix分页可重复表头及垂直滚动时浮动表头
- javascript实现多表头分类交叉报表之二:效果图
- javascript实现多表头分类交叉报表之二:效果图
- javascript实现多表头分类交叉报表之二:效果图
- javascript实现多表头分类交叉报表之二:效果图
- javascript实现多表头分类交叉报表之二:效果图
- javascript实现多表头分类交叉报表之二:效果图
- javascript实现多表头分类交叉报表之二:效果图
- javascript实现多表头分类交叉报表之二:效果图
- 1008. Elevator (20)
- 记一次面试
- python:运算符
- Web
- 以/为单位截取URL
- SQL学习之双向表头报表查询
- 使用Git管理代码
- Python的几个问题
- Entity Framework(3)删除
- echarts统计图json格式数据来自单元格
- think in computer vision
- OC中NSLog函数输出格式详解
- Java NIO原理 图文分析及代码实现
- visual studio中头文件和库文件路径设置