实用Sql总括wwwlehu6.vip乐虎官网

wwwlehu6.vip乐虎官网 1wwwlehu6.vip乐虎官网 2Code
计算表中一列中各种型的食指还要每1个高校的总计结果用一行展现  
  大学      总人数    结业人数    结业人数
  人艺术高校    300        298          2
  计算机      320        299          1
    wwwlehu6.vip乐虎官网 3wwwlehu6.vip乐虎官网 4wwwlehu6.vip乐虎官网 5wwwlehu6.vip乐虎官网 6wwwlehu6.vip乐虎官网 7wwwlehu6.vip乐虎官网 8wwwlehu6.vip乐虎官网 9wwwlehu6.vip乐虎官网 10wwwlehu6.vip乐虎官网 11wwwlehu6.vip乐虎官网 12wwwlehu6.vip乐虎官网 13wwwlehu6.vip乐虎官网 14.
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        ———————————————————————–

 

总计各样高校年龄段人数

SELECT bmmc, CASE WHEN DATEDIFF(YY, cssj, GETDATE())
      < 20 THEN ‘0-19岁’ WHEN DATEDIFF(YY, cssj, GETDATE()) BETWEEN
20 AND
      29 THEN ’20-29岁’ WHEN DATEDIFF(YY, cssj, GETDATE()) BETWEEN 30
AND
      39 THEN ’30-39岁’ WHEN DATEDIFF(YY, cssj, GETDATE()) BETWEEN 40
AND
      50 THEN ’40-50岁’ ELSE ’50岁以上’ END AS age
FROM jsjbxx
ORDER BY age

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图