sql

select '本科毕业生' a,
       nvl(t.b, 0) b,
       round(nvl(t.b, 0)/t.d*100,2)||'%' e,  
       nvl(t.c, 0) c,
       round(nvl(t.c, 0) /t.d*100,2)||'%' f  
        from
(select '1' hdss, count(*) bkshznum
          from (select * from jy_jyxt_syxxb 
         WHERE xlccdm = '31'
           and byzd5 = '1' and mzdm='01') where bynf='2014'
           union all

select '2' hdss, count(*) bksssmznum
          from (select * from jy_jyxt_syxxb 
         WHERE xlccdm = '31'
           and byzd5 = '1' and mzdm<>'01') where bynf='2014'
 union all
 select '3' hdss, count(*) zrs
          from (select * from jy_jyxt_syxxb 
         WHERE xlccdm = '31'
           and byzd5 = '1') where bynf='2014')
      
           
           pivot(sum(bkshznum)
   for hdss in('1' as b, '2' as c, '3' as d)) t
   union all
select '毕业研究生' a,
       nvl(t.b, 0) b,
       round(nvl(t.b, 0)/t.d*100,2)||'%' e,  
       nvl(t.c, 0) c,
       round(nvl(t.c, 0) /t.d*100,2)||'%' f  
        from
(select '1' hdss, count(*) bkshznum
          from (select * from jy_jyxt_syxxb 
         WHERE xlccdm = '11'
           and byzd5 = '1' and mzdm='01') where bynf='2014'
           union all

select '2' hdss, count(*) bksssmznum
          from (select * from jy_jyxt_syxxb 
         WHERE xlccdm = '11'
           and byzd5 = '1' and mzdm<>'01') where bynf='2014'
 union all
 select '3' hdss, count(*) zrs
          from (select * from jy_jyxt_syxxb 
         WHERE xlccdm = '11'
           and byzd5 = '1') where bynf='2014')
      
           
           pivot(sum(bkshznum)
   for hdss in('1' as b, '2' as c, '3' as d)) t
     
   union all
select '总体' a,
       nvl(t.b, 0) b,
       round(nvl(t.b, 0)/t.d*100,2)||'%' e,  
       nvl(t.c, 0) c,
       round(nvl(t.c, 0) /t.d*100,2)||'%' f  
        from
(select '1' hdss, count(*) ztbkshznum
          from (select * from jy_jyxt_syxxb 
         WHERE byzd5 = '1' and mzdm='01' and (xlccdm = '31' or xlccdm = '11')) where bynf='2014'
           union all

   select '2' hdss, count(*) bksssmznum
          from (select * from jy_jyxt_syxxb 
         WHERE byzd5 = '1' and mzdm<>'01' and (xlccdm = '31' or xlccdm = '11')) where bynf='2014'
 union all
    select '3' hdss, count(*) zrs
          from (select * from jy_jyxt_syxxb 
         WHERE byzd5 = '1' and (xlccdm = '31' or xlccdm = '11')
           ) where 1=1 and bynf='2014')
      
           
           pivot(sum(ztbkshznum)
   for hdss in('1' as b, '2' as c, '3' as d)) t

• round 除法留几位小数点

• pivot 行列转换

• nvl(t.b, 0) 如果t.b是null，那么返回0

• substr 截取字符，例如（1994-06-09），sj='1994-06-09' substr(sj,0,4) 就会把1994筛选出来

• distinct重复的值只取一个，例如，时间筛选出来有很多条记录且时间是一样的，例如1994，多条，那么执行如下sql，就只筛选出一个

select distinct(dlsj) sj from (select substr(sj,0,4) sj from JY_JYWZ_YHDLJLB 
            where sj is not null  order by sj asc)

• 视图：类似于联表查询的结果，只能查询