oracle_统计数量

最新推荐文章于 2024-11-05 14:55:30 发布

原创最新推荐文章于 2024-11-05 14:55:30 发布 · 785 阅读

0 ·

CC 4.0 BY-SA版权

oracel 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于统计不同地区学生的各种属性分布情况，包括性别、是否结业等，并关联了学生所在地区的相关信息。此SQL涉及多个子查询及连接操作，展示了如何使用DECODE函数进行条件计数。

select k.title,k.code,A1,A2,A3,B1,B2,C1,C2,C3,C4,C5,D,E,F,M,N,P,boy,girl,weijie,yijie,k.yidi
from
(select
distinct(substr(stu_area_code,1,4)) areacode,
count(DECODE(stu_model,'A1',stu_model))  A1,
count(DECODE(stu_model,'A2',stu_model)) A2,
count(DECODE(stu_model,'A3',stu_model)) A3,
count(DECODE(stu_model,'B1',stu_model)) B1,
count(DECODE(stu_model,'B2',stu_model)) B2,
count(DECODE(stu_model,'C1',stu_model)) C1,
count(DECODE(stu_model,'C2',stu_model)) C2,
count(DECODE(stu_model,'C3',stu_model)) C3,
count(DECODE(stu_model,'C4',stu_model)) C4,
count(DECODE(stu_model,'C5',stu_model)) C5,
count(DECODE(stu_model,'D',stu_model)) D,
count(DECODE(stu_model,'E',stu_model)) E,
count(DECODE(stu_model,'F',stu_model)) F,
count(DECODE(stu_model,'M',stu_model)) M,
count(DECODE(stu_model,'N',stu_model)) N,
count(DECODE(stu_model,'P',stu_model)) P,
count(DECODE(stu_sex,'ÄÐ',stu_sex)) boy,
count(DECODE(stu_sex,'Å®',stu_sex)) girl,
count(DECODE(stu_isend,'0',stu_isend)) weijie,
count(DECODE(stu_isend,'1',stu_isend)) yijie
from sys_student  group by substr(stu_area_code,1,4)) x
right outer join
(select y.title,y.code,z.yidi from
(select area.area_title title,substr(area.area_code,1,4) code from sys_area area
where substr(area.area_code,0,2)='21' and substr(area.area_code,3,2)<>00
and substr(area.area_code,5,2)='00') y
left outer join (select
distinct(substr(stu_area_code,1,4)) areacode,
count(stu.stu_id) yidi
from sys_student  stu
where substr(stu.stu_area_code,0,4)<>substr(stu.stu_reg_add_code,0,4)
group by substr(stu.stu_area_code,1,4)) z
on z.areacode=y.code
) k
on k.code=x.areacode
order by k.code