如何运用SQL进行双向表头报表查询
接到银行业务需求,要做一个如图的个人年龄区间存款统计报表:
思路:
可以看到,表头是分纵向和横向的,因此首先想到的是需要进行两次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_B FROM 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_B FROM C_CUST_AGE WHERE DEPT_ID = ? AND TYPE = ? GROUP BY TYPE, AGE, BAL) GROUP BY TYPE,bal_range,AGE
查询结果如下:
观察最前面那张报表示例图可知,实际的字段数据可以分为30岁以下的户数、30岁以下的余额、30-40岁的户数、30-40岁的余额······由于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_bal FROM(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_B FROM C_CUST_AGE WHERE DEPT_ID = ? AND TYPE = ? GROUP BY TYPE, AGE, BAL) GROUP BY TYPE,bal_range,AGE) group by type,bal_range
最终结果集:
上一篇: JSP_通过表格显示数据库的信息
下一篇: struts2启动过程分析