雪山飛鵠

溫馨提示：您的每一次轉(zhuǎn)載,體現(xiàn)了我寫(xiě)此文的意義!!!煩請(qǐng)您在轉(zhuǎn)載時(shí)注明出處http://www.aygfsteel.com/sxyx2008/謝謝合作!!!

BlogJava

管理

215 Posts :: 1 Stories :: 674 Comments :: 0 Trackbacks

公告

常用鏈接

留言簿(18)

隨筆分類(222)

隨筆檔案(214)

文章檔案(1)

2010年11月 (1)

收藏夾(67)

我的鏈接

llying
Android
boobooke
codehaus
css+div
cygwin
extjs
Flex4.1在線文檔
Flex4教程
flex-explorer
Google Map API
JavaWeb
JAVA面試
Maven中文
rss2.0
springside
xfire官方開(kāi)發(fā)指南
中國(guó)手機(jī)開(kāi)發(fā)網(wǎng)
挺不錯(cuò)的學(xué)習(xí)筆記
標(biāo)簽大全
正則表達(dá)式
熔巖技術(shù)博客
語(yǔ)法高亮插件
阿蜜果
隔葉黃鶯

搜索

閱讀排行榜

評(píng)論排行榜

SQL WHEN THEN 示例

create view zbfrfb
(xzqh,ct1,ct2,ct3,ct4,ct5)
as
select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh not  in('612700','612600','610200','610500','610400',
'610300','610100','612500','612300')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh
UNION ALL
--榆林
select '612700' xzqh, sum(ct1)ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('612700','612701')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) yulin
UNION ALL
--延安
select '612600' xzqh, sum(ct1)ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('612600','612601')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) yanan
UNION ALL
--銅川
select '610200' xzqh, sum(ct1)ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('610200','610202','610203')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) tongchan
UNION ALL
--渭南
select '610500' xzqh, sum(ct1) ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('610500','610502')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) weinan
UNION ALL
--漢中
select '612300' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('612300','612301')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) hanzhong
UNION ALL
--商州市
select '612500' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('612500','612501')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) shangzhou
UNION ALL
--西安市
select '610100' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('610100','610102','610103','610104','610111','610112','610113','610114')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) xian
UNION ALL
--寶雞市
select '610300' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('610303','610321','610300')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) baoji
UNION ALL
--咸陽(yáng)市
select '610400' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select  zzjgdm,xzqh from zzjgdmbz where xzqh in('610400','610402','610404','616900')) t left  join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) xianyang

posted on 2010-11-07 10:02 雪山飛鵠閱讀(4607) 評(píng)論(0) 編輯收藏

新用戶注冊(cè) 刷新評(píng)論列表


只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問(wèn) 管理