SQL复杂查询案例-优快云博客

做项目的时候遇到一个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