sql使用总结1.1版

做项目的时候遇到一个sql的问题，按表中某一字段的不同记录做不同的处理，要用到select case when语句的嵌套以及group by语句，想了一下午总算写出来了，现在把成果贴出来：

select CLLX_MX, lx, dw,
	--目标值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2008 and a.yf=5 then a.mbz end) else avg(case when a.nf=2008 then a.mbz end) end as mbz,
	--去年目标值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2007 and a.yf=5 then a.mbz end) else avg(case when a.nf=2007 then a.mbz end) end as qn_mbz,
	--本期实际值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2008 and a.yf=5 then a.SJZ end) else sum(case when a.nf = 2008 and a.yf <= 7 and a.yf >= 2 then a.SJZ end) end as SJZ,
	--去年同期实际值
	case a.CLLX_MX when '期末库存' then max(case when a.nf=2007 and a.yf=5 then a.SJZ end) else sum(case when a.nf = 2007 and a.yf <= 7 and a.yf >= 2 then a.SJZ end) end as QN_SJZ,
	--今年累计实际值
	case a.CLLX_MX when '期末库存' then 0 else sum(case when a.nf = 2008 and a.yf <= 8 then SJZ end) end as LJ_SJZ,
	--去年同期累计实际值
	case a.CLLX_MX when '期末库存' then 0 else sum(case when a.nf = 2007 and a.yf <= 8 then SJZ end) end as LJ_QN_SJZ
		
	from tableName a
	where tjlb = '合计'
	group by CLLX, CLLX_MX, xh, lx, dw order by xh

如上所示，case when语句是可以实现嵌套的，巧用case when不仅提高了效率而且可以避免用自己和自己join可能出现的错误，而对于group by子句的使用，要么select的字段要同样放进group by，要么该字段要放进聚合函数，否则会报错！

再贴几个项目里用到的sql

select xmmx, lx, dw, bz,
	--月计划
	max(case when a.nf=2008 and a.yf=5 then YJH end) as YJH,
	--本月实际
	max(case when a.nf=2008 and a.yf=5 then SJZ end) as SJZ,
	--今年累计实际值
	case when a.xmmx like '%率' or a.xmmx like '%平均人数' or a.xmmx like '%技术指标' or a.xmmx like '%主要产品产量' then max(case when a.nf=2008 and a.yf=5 then SJZ_LJ end) else sum(case when a.nf=2008 and a.yf <=5 then SJZ end) end as LJ_SJZ,
	--case when charindex('现行价', a.xmmx)>0 or charindex('1', a.xmmx)>0 then sum(case when a.nf=2008 and a.yf <=5 then SJZ end) else max(case when a.nf=2008 and a.yf=5 then SJZ_LJ end) end as LJ_SJZ,
	--去年同期累计实际值
	case when a.xmmx like '%率' or a.xmmx like '%平均人数' or a.xmmx like '%技术指标' or a.xmmx like '%主要产品产量' then max(case when a.nf=2007 and a.yf=5 then SJZ_LJ end) else sum(case when a.nf=2007 and a.yf <=5 then SJZ end) end as LJ_QN_SJZ

from tableName a
where  nf = 2008 or nf = 2007
group by xm, xmmx, xh, lx, dw, bz order by xh

select (case when z.xxmmx is null then z.yxmmx else z.xxmmx end) as xmmx, 
            (case when z.xlx is null then z.ylx else z.xlx end) as lx,
	   z.xmbz_zj, z.xmbz_kys, z.xqn_mbz_zj, z.xqn_mbz_kys,
	   z.xsjz_zj, z.xsjz_kys, z.xQN_SJZ_ZJ, z.xQN_SJZ_KYS, z.xLJ_SJZ_ZJ, z.xLJ_SJZ_KYS, z.xLJ_QN_SJZ_ZJ, z.xLJ_QN_SJZ_KYS,
	   z.ymbz_zj, z.ymbz_kys, z.yqn_mbz_zj, z.yqn_mbz_kys,
	   z.ysjz_zj, z.ysjz_kys, z.yQN_SJZ_ZJ, z.yQN_SJZ_KYS, z.yLJ_SJZ_ZJ, z.yLJ_SJZ_KYS, z.yLJ_QN_SJZ_ZJ, z.yLJ_QN_SJZ_KYS   
from (

select x.xmmx as xxmmx, y.xmmx as yxmmx, x.lx as xlx, y.lx as ylx, x.mbz_zj as xmbz_zj, x.mbz_kys as xmbz_kys, x.qn_mbz_zj as xqn_mbz_zj, x.qn_mbz_kys as xqn_mbz_kys,
	   x.sjz_zj as xsjz_zj, x.sjz_kys as xsjz_kys, x.QN_SJZ_ZJ as xQN_SJZ_ZJ, x.QN_SJZ_KYS as xQN_SJZ_KYS, x.LJ_SJZ_ZJ as xLJ_SJZ_ZJ,
	   x.LJ_SJZ_KYS as xLJ_SJZ_KYS, x.LJ_QN_SJZ_ZJ as xLJ_QN_SJZ_ZJ, x.LJ_QN_SJZ_KYS as xLJ_QN_SJZ_KYS,
	   y.mbz_zj as ymbz_zj, y.mbz_kys as ymbz_kys, y.qn_mbz_zj as yqn_mbz_zj, y.qn_mbz_kys as yqn_mbz_kys, y.sjz_zj as ysjz_zj,
	   y.sjz_kys as ysjz_kys, y.QN_SJZ_ZJ as yQN_SJZ_ZJ, y.QN_SJZ_KYS as yQN_SJZ_KYS, y.LJ_SJZ_ZJ as yLJ_SJZ_ZJ,
	   y.LJ_SJZ_KYS as yLJ_SJZ_KYS, y.LJ_QN_SJZ_ZJ as yLJ_QN_SJZ_ZJ, y.LJ_QN_SJZ_KYS as yLJ_QN_SJZ_KYS
from
	   (select a.xmmx as xmmx, a.lx as lx,
	   --目标值
	   avg(case when a.nf=2008 then mbz_zj end) as mbz_zj,
	   avg(case when a.nf=2008 then mbz_kys end) as mbz_kys,
	   --去年目标值
	   avg(case when a.nf=2007 then mbz_zj end) as qn_mbz_zj,
	   avg(case when a.nf=2007 then mbz_kys end) as qn_mbz_kys,
	   --本期实际值 
	   sum(case when a.nf = 2008 and a.yf <= 10 and a.yf >= 2 then SJZ_ZJ end) as SJZ_ZJ,
	   sum(case when a.nf = 2008 and a.yf <= 10 and a.yf >= 2 then SJZ_KYS end) as SJZ_KYS,
	   --去年同期实际值
	   sum(case when a.nf = 2007 and a.yf <= 10 and a.yf >= 2 then SJZ_ZJ end) as QN_SJZ_ZJ,
	   sum(case when a.nf = 2007 and a.yf <= 10 and a.yf >= 2 then SJZ_KYS end) as QN_SJZ_KYS,
	   --今年累计实际值
	   sum(case when a.nf = 2008 and a.yf <= 10 then SJZ_ZJ end) as LJ_SJZ_ZJ,
	   sum(case when a.nf = 2008 and a.yf <= 10 then SJZ_KYS end) as LJ_SJZ_KYS,
	   --去年同期累计实际值
	   sum(case when a.nf = 2007 and a.yf <= 10 then SJZ_ZJ end) as LJ_QN_SJZ_ZJ,
	   sum(case when a.nf = 2007 and a.yf <= 10 then SJZ_KYS end) as LJ_QN_SJZ_KYS
	   from tableName a
	   where a.tjlb = '项目数' and a.tjfw = '合计' and (a.nf = 2008 or a.nf = 2007)
	   group by a.xm, a.xmmx, a.xh, a.lx
	   order by a.xh) x
full outer join
	   (select b.xmmx as xmmx, b.lx as lx,
	   --目标值
	   avg(case when b.nf=2008 then mbz_zj end) as mbz_zj,
	   avg(case when b.nf=2008 then mbz_kys end) as mbz_kys,
	   --去年目标值
	   avg(case when b.nf=2007 then mbz_zj end) as qn_mbz_zj,
	   avg(case when b.nf=2007 then mbz_kys end) as qn_mbz_kys,
	   --本期实际值 
	   sum(case when b.nf = 2008 and b.yf <= 10 and b.yf >= 2 then SJZ_ZJ end) as SJZ_ZJ,
	   sum(case when b.nf = 2008 and b.yf <= 10 and b.yf >= 2 then SJZ_KYS end) as SJZ_KYS,
	   --去年同期实际值
	   sum(case when b.nf = 2007 and b.yf <= 10 and b.yf >= 2 then SJZ_ZJ end) as QN_SJZ_ZJ,
	   sum(case when b.nf = 2007 and b.yf <= 10 and b.yf >= 2 then SJZ_KYS end) as QN_SJZ_KYS,
	   --今年累计实际值
	   sum(case when b.nf = 2008 and b.yf <= 10 then SJZ_ZJ end) as LJ_SJZ_ZJ,
	   sum(case when b.nf = 2008 and b.yf <= 10 then SJZ_KYS end) as LJ_SJZ_KYS,
	   --去年同期累计实际值
	   sum(case when b.nf = 2007 and b.yf <= 10 then SJZ_ZJ end) as LJ_QN_SJZ_ZJ,
	   sum(case when b.nf = 2007 and b.yf <= 10 then SJZ_KYS end) as LJ_QN_SJZ_KYS
	   from tableName b
	   where b.tjlb = '预计用款' and b.tjfw = '合计' and (b.nf = 2008 or b.nf = 2007)
	   group by b.xm, b.xmmx, b.xh, b.lx
	   order by b.xh) y
on x.xmmx = y.xmmx
) as z

评论

3 楼 cuishen 2009-06-09  

最近在用Oracle数据库做项目，刚写出一个很复杂的SQL，贴出来和大家分享：

    select
    c.busi_no, c.BANK_ID, c.IDTYPE_ID, c.LEFTPAY_MONTHS, c.ID_NO, c.ID_CTRY, c.UPDATE_DATE, c.BUSITYPE_ID, 
         c.BUSIDETTYPE_ID, c.LOCUS_ID, c.LOCUS_NAME, c.OPEN_DATE, c.TERM_DATE,
           c.CURRENCY_ID, c.CREDIT_AMT, c.SHARECREDIT_AMT, c.MAXDEBT_AMT, c.GUARANTTYPE_ID, c.PAYFREQ_ID, c.PAY_MONTHS, c.LASTPAY_DATE,
           c.CURROVER_TIMES, c.BREACH_TIMES, c.MAXOVER_TIMES, c.FIVE_STATUS, c.ACCTSTAT_ID, c.RETURN_STATUS,
           nvl(c.OVER_AMT, 0) as OVER_AMT, c.bill_date, nvl(c.OVER30CAP_AMT, 0) as OVER30CAP_AMT, nvl(c.OVER60CAP_AMT, 0) as OVER60CAP_AMT, 
           nvl(c.OVER90CAP_AMT, 0) as OVER90CAP_AMT, nvl(c.OVER180CAP_AMT, 0) as OVER180CAP_AMT, nvl(d.EXPPAY_AMT, 0) as EXPPAY_AMT, 
           nvl(d.REALPAY_AMT, 0) as REALPAY_AMT
    from
    (select a.busi_no, a.BANK_ID, a.IDTYPE_ID, a.LEFTPAY_MONTHS, a.ID_NO, a.ID_CTRY, a.UPDATE_DATE, a.BUSITYPE_ID, 
         a.BUSIDETTYPE_ID, a.LOCUS_ID, e.REMOTE_NAME as LOCUS_NAME, a.OPEN_DATE, a.TERM_DATE,
           a.CURRENCY_ID, a.CREDIT_AMT, a.SHARECREDIT_AMT, a.MAXDEBT_AMT, a.GUARANTTYPE_ID, a.PAYFREQ_ID, a.PAY_MONTHS, a.LASTPAY_DATE,
           a.CURROVER_TIMES, a.BREACH_TIMES, a.MAXOVER_TIMES, a.FIVE_STATUS, a.ACCTSTAT_ID, a.RETURN_STATUS,
           b.OVER_AMT, b.bill_date, b.OVER30CAP_AMT, b.OVER60CAP_AMT, b.OVER90CAP_AMT, b.OVER180CAP_AMT
      from (select busi_no, BANK_ID, IDTYPE_ID, ID_NO, ID_CTRY, UPDATE_DATE, BUSITYPE_ID, BUSIDETTYPE_ID, LOCUS_ID, OPEN_DATE, TERM_DATE,
           CURRENCY_ID, CREDIT_AMT, SHARECREDIT_AMT, MAXDEBT_AMT, GUARANTTYPE_ID, PAYFREQ_ID, PAY_MONTHS, LASTPAY_DATE,
           CURROVER_TIMES, BREACH_TIMES, MAXOVER_TIMES, FIVE_STATUS, ACCTSTAT_ID, RETURN_STATUS,
                  (case when ((TO_CHAR(TERM_DATE, 'YYYY') - TO_CHAR(DATA_DATE, 'YYYY')) * 12 +
                   (TO_CHAR(TERM_DATE, 'MM') - TO_CHAR(DATA_DATE, 'MM'))) > 0 then 
                   ((TO_CHAR(TERM_DATE, 'YYYY') - TO_CHAR(DATA_DATE, 'YYYY')) * 12 +
                   (TO_CHAR(TERM_DATE, 'MM') - TO_CHAR(DATA_DATE, 'MM'))) else 0 end)AS LEFTPAY_MONTHS
              from crd_acc_info
             where busi_no = '430700151201100'
               ) a    
      left join (select busi_no, max(BILL_DATE) as bill_date,
                        SUM(CASE
                              WHEN COALESCE(EXPCAP_AMT + EXPINT_AMT + EXPINTPEN_AMT,
                                            0) <= COALESCE(REALCAP_AMT + REALINT_AMT +
                                                           REALINTPEN_AMT,
                                                           0) THEN 0
                              ELSE
                               COALESCE(EXPCAP_AMT + EXPINT_AMT + EXPINTPEN_AMT -
                                        REALCAP_AMT - REALINT_AMT - REALINTPEN_AMT,
                                        0)
                            END) AS OVER_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 30 AND
                                   TO_DATE(DATA_DATE) - BILL_DATE <= 60 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER30CAP_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 60 AND
                                   TO_DATE(DATA_DATE) - BILL_DATE <= 90 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER60CAP_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 90 AND
                                   TO_DATE(DATA_DATE) - BILL_DATE <= 180 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER90CAP_AMT,
                        SUM(CASE
                              WHEN TO_DATE(DATA_DATE) - BILL_DATE > 180 THEN
                               COALESCE(EXPCAP_AMT - REALCAP_AMT, 0)
                              ELSE 0
                            END) AS OVER180CAP_AMT
                 
                   from crd_ret_info
                  where busi_no = '430700151201100'
                  group by busi_no) b on a.busi_no = b.busi_no
                  left join (select remote_code, REMOTE_NAME from stand_code where table_id = 'LOCUS')e
                  on a.LOCUS_ID = e.remote_code
                  ) c
      left join 
      (select busi_no, (nvl(k.expcap_amt,0) + nvl(k.expint_amt,0) + nvl(k.expintpen_amt,0)) as EXPPAY_AMT, 
              (nvl(k.realcap_amt,0) + nvl(k.realint_amt,0) + nvl(k.realintpen_amt,0)) as REALPAY_AMT 
              from crd_ret_info k
              where busi_no = '430700151201100' and BILL_DATE = (select max(BILL_DATE)as BILL_DATE 
                                              from crd_ret_info 
                                              where busi_no = '430700151201100')) d
      on c.busi_no = d.busi_no  

2 楼 cuishen 2008-12-09  

duronshi 写道

个人感觉以上写的SQL执行效率不高,执行时间应该不是很理想

以上sql是针对DB2数据库的，DB2数据库反而倾向于让开发者写复杂的join语句和查询语句，因为它对复杂的查询做了特别的优化，以上sql代码都是取自项目代码，在生产环境运行正常，而且速度也是不慢的！

1 楼 duronshi 2008-09-10  

个人感觉以上写的SQL执行效率不高,执行时间应该不是很理想