求一种select查詢方法
表aot_rest
badge name depid type num
008466zhuqiangca0j12.0
008466zhuqiangca0j13.0
008466zhuqiangca0j23.0
008466zhuqiangca0j26.0
008466zhuqiangca0j26.0
008466zhuqiangca0j36.0
008466zhuqiangca0j19.0
008466zhuqiangca0j39.0
000442xinyuecAOj12.0
000442xinyuecAOj23.0
000442xinyuecAOj13.0
如何select變爲以下的類型
badge name depid j1 j2 j3
008466 zhuqiang cao 14.0 15.0 15.0
000442 xinyue cAO 5.0 3.0
[解决办法]
select badge,name,depid,j1=sum(case when type= 'j1 ' then num end),j2=sum(case when type= 'j2 ' then num end),j3=sum(case when type= 'j3 ' then num end) from aot_rest group by badge,name,depid
[解决办法]
create table aot_rest(badge varchar(10), name varchar(10), depid varchar(10), type varchar(10), num decimal(10,1))
insert aot_rest select '008466 ', 'zhuqiang ', 'ca0 ', 'j1 ',2.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j1 ',3.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j2 ',3.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j2 ',6.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j2 ',6.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j3 ',6.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j1 ',9.0
union all select '008466 ', 'zhuqiang ', 'ca0 ', 'j3 ',9.0
union all select '000442 ', 'xinyue ', 'cAO ', 'j1 ',2.0
union all select '000442 ', 'xinyue ', 'cAO ', 'j2 ',3.0
union all select '000442 ', 'xinyue ', 'cAO ', 'j1 ',3.0
select badge, name, depid,
[j1]=sum(case when type= 'j1 ' then num end),
[j2]=sum(case when type= 'j2 ' then num end),
[j3]=sum(case when type= 'j3 ' then num end)
from aot_rest
group by badge, name, depid
--result
badge name depid j1 j2 j3
---------- ---------- ---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
000442 xinyue cAO 5.0 3.0 NULL
008466 zhuqiang ca0 14.0 15.0 15.0
(2 row(s) affected)
