有兩個表
一個是:
zdh fkcode skje
1 RMB 200
20 RMB 1000
20 CN 200
20 XFZ 1200
23 RMB 100
24 xfz 100另一個是
zdh card
1 333
20 444
23 555
24 666
如何用語句寫成:
zdh card RMB CN XFZ
1 333 200 0 0
20 444 1000 200 1200
23 555 100 0 0
24 666 0 0 100
注意 fkcode 是有多種類型,並不是隻有 RMB CN XFZ三種!
form table1,table2
where table1.zdh =table2.zdh
呵呵,那有這樣簡單!看清楚我的要求!
form table1,table2
where table1.zdh =table2.zdh
呵呵,那有這樣簡單!看清楚我的要求!
(select Table1.skje from Table1 where Table1.Zdh = Table2.Zdh and Table1.fkcode='RMB') as RMB,
(select Table1.skje from Table1 where Table1.Zdh = Table2.Zdh and Table1.fkcode='CN') as CN,
(select Table1.skje from Table1 where Table1.Zdh = Table2.Zdh and Table1.fkcode='XFZ') as XFZ
from table2Result
Zdh Card RMB CN XFZ
----------- ----------- --------------------- --------------------- ---------------------
1 333 200.0000 NULL NULL
20 444 1000.0000 200.0000 1200.0000
23 555 100.0000 NULL NULL
24 666 NULL NULL 100.0000但如果你的Fkcode是不定个数据的,可以用一个循环把“(select Table1.skje from Table1 where Table1.Zdh = Table2.Zdh and Table1.fkcode='CN') as CN,”把各种类型列出来。
table2 t,
(selct sum(skje) skje,zdh from table1 where fkcode='rmb' group by zdh) a,
(selct sum(skje) skje,zdh from table1 where fkcode='xfz' group by zdh) b,
(selct sum(skje) skje,zdh from table1 where fkcode='cn' group by zdh) c
where t.zdh=a.zdh and t.zdh=b.zdh and t.zdh=c.zdh
不好搞呀!
table2 t,
(selct sum(skje) skje,zdh from table1 where fkcode='rmb' group by zdh) a,
(selct sum(skje) skje,zdh from table1 where fkcode='xfz' group by zdh) b,
(selct sum(skje) skje,zdh from table1 where fkcode='cn' group by zdh) c
where t.zdh=a.zdh and t.zdh=b.zdh and t.zdh=c.zdh
没看到很有规律吗??
循环就行了撒
select @SubSql = ''Declare FkCode Cursor for
select fkcode from table1 group by fkcode
open Fkcode
FETCH NEXT FROM Fkcode INTO @Fieldstr
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
PRINT @fIELDSTR
select @subsql = @subsql + '(select Table1.skje from Table1 where Table1.Zdh = Table2.Zdh and Table1.fkcode='''+RTRIM(@fieldstr)+''') as '+rTRIM(@fieldstr) +','
Print @SUBSQL
END
else
print 'This is Error'
FETCH NEXT FROM fkcode INTO @Fieldstr
END
CLOSE fkcode
Deallocate fkcode select @subSQL ='select Table2.Zdh,Table2.Card,' +@subSQL+ ''''' as nil from table2'
eXEC(@subSQl)
select @SubSql = ''Declare FkCode Cursor for
select fkcode from table1 group by fkcode
open Fkcode
FETCH NEXT FROM Fkcode INTO @Fieldstr
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
select @subsql = @subsql + '(select Table1.skje from Table1 where Table1.Zdh = Table2.Zdh and Table1.fkcode='''+RTRIM(@fieldstr)+''') as '+rTRIM(@fieldstr) +','
END
FETCH NEXT FROM fkcode INTO @Fieldstr
END
CLOSE fkcode
Deallocate fkcodeselect @subSQL ='select Table2.Zdh,Table2.Card,' +LEFT(@subSQL,LEN(@subSQL)-1)+' from table2'
eXEC(@subSQl)
---------------------------------------------------------------------------------
Result
Zdh Card CN RMB XFZ
----------- ----------- --------------------- --------------------- ---------------------
1 333 NULL 200.0000 NULL
20 444 200.0000 1000.0000 1200.0000
23 555 NULL 100.0000 NULL
24 666 NULL NULL 100.0000---------------------------------------------------------------------------------
让我还学会了
FETCH的用法了~