sql统计
统计表中一列中各类型的人数而且每一个学院的统计结果用一行显示
学院 总人数 毕业人数 结业人数
人文学院 300 298 2
计算机 320 299 1
.....................................
sql语句:
select jw_xy, count(jw_xy) as 总人数,sum(case when jw_bzh='毕业' then 1 else 0 end) as 毕业人数,sum(case when jw_bzh='结业' then 1 else 0 end) as 结业人数 from jwc_GraduationAudit group by jw_xy
---------------------------------
求一列各种情况之和(每一种结果以行显示)
create view V_xscj_kcjbxx
as
select xh, kcxz,xscj.kcxf from xscj,kcjbxx where xscj.kcbh=kcjbxx.kcbh
select xh,kcxz, sum(kcxf) as total from V_xscj_kcjbxx group by xh,kcxz order by xh
显示结果:
xh kcxz total
040121001 选修课 3.0
040121001 必修课 179.5
040121001 任意选修课 5.0
040121001 限定选修课 1.5
----------------------------------
select xh,(select sum(kcxf) where kcxz='选修课' ) as 选修课 , (select sum(kcxf) where kcxz='必修课') as 必修课,(select sum(kcxf) where kcxz='任意选修课') as 任意选修课,(select sum(kcxf) where kcxz='限定选修课') as 限定选修课
from V_xscj_kcjbxx group by xh,kcxf order by xh
显示结果:
xh 选修课 限定选修课 必修课 任意选修课 040121001 3.0 NULL NULL NULL 040121001 NULL NULL NULL 179.5 040121001 NULL NULL 5.0 NULL 040121001 NULL 1.5 NULL NULL
----------------------------
求一列各种情况之和(每一种结果以列显示)
select xh,
sum(case kcxz when '选修课' then kcxf end) as 选修课,
sum(case kcxz when '必修课' then kcxf end) as 必修课,
sum(case kcxz when '任意选修课' then kcxf end) as 任意选修课,
sum(case kcxz when '限定选修课' then kcxf end) as 限定选修课
from V_xscj_kcjbxx group by xh order by xh
结果显示:
xh 选修课 限定选修课 必修课 任意选课 040121001 3.0 5.0 179.5 1.0 179.5 -----------------------------------------------------------------------
学院 总人数 毕业人数 结业人数
人文学院 300 298 2
计算机 320 299 1
.....................................
sql语句:
select jw_xy, count(jw_xy) as 总人数,sum(case when jw_bzh='毕业' then 1 else 0 end) as 毕业人数,sum(case when jw_bzh='结业' then 1 else 0 end) as 结业人数 from jwc_GraduationAudit group by jw_xy
---------------------------------
求一列各种情况之和(每一种结果以行显示)
create view V_xscj_kcjbxx
as
select xh, kcxz,xscj.kcxf from xscj,kcjbxx where xscj.kcbh=kcjbxx.kcbh
select xh,kcxz, sum(kcxf) as total from V_xscj_kcjbxx group by xh,kcxz order by xh
显示结果:
xh kcxz total
040121001 选修课 3.0
040121001 必修课 179.5
040121001 任意选修课 5.0
040121001 限定选修课 1.5
----------------------------------
select xh,(select sum(kcxf) where kcxz='选修课' ) as 选修课 , (select sum(kcxf) where kcxz='必修课') as 必修课,(select sum(kcxf) where kcxz='任意选修课') as 任意选修课,(select sum(kcxf) where kcxz='限定选修课') as 限定选修课
from V_xscj_kcjbxx group by xh,kcxf order by xh
显示结果:
xh 选修课 限定选修课 必修课 任意选修课 040121001 3.0 NULL NULL NULL 040121001 NULL NULL NULL 179.5 040121001 NULL NULL 5.0 NULL 040121001 NULL 1.5 NULL NULL
----------------------------
求一列各种情况之和(每一种结果以列显示)
select xh,
sum(case kcxz when '选修课' then kcxf end) as 选修课,
sum(case kcxz when '必修课' then kcxf end) as 必修课,
sum(case kcxz when '任意选修课' then kcxf end) as 任意选修课,
sum(case kcxz when '限定选修课' then kcxf end) as 限定选修课
from V_xscj_kcjbxx group by xh order by xh
结果显示:
xh 选修课 限定选修课 必修课 任意选课 040121001 3.0 5.0 179.5 1.0 179.5 -----------------------------------------------------------------------
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步