SQL Server數(shù)據(jù)庫多表關(guān)聯(lián)匯總查詢的問題解決
SQL Server數(shù)據(jù)庫多表關(guān)聯(lián)匯總查詢是我們經(jīng)常用到的，本文我們就介紹了一個(gè)多表關(guān)聯(lián)匯總查詢的實(shí)例，通過這個(gè)實(shí)例在多表關(guān)聯(lián)查詢中遇到的問題以及它的解決方法讓我們一起來了解一下SQL Server數(shù)據(jù)庫多表關(guān)聯(lián)匯總查詢的相關(guān)知識(shí)吧，希望本次的介紹能夠?qū)δ兴鶐椭?/p>
- select isnull(s.mnumber,ss.mnumber) mnumber,isnull(m.whcode,ss.whcode) whcode,
- isnull(sum(factreceiptquan),0)-isnull(sum(factissuequan),0) + sum(isnull(ss.quan,0)) quan
- from (select * from gy_inoutmain where billcode in('1201','1202','1203','1204','1205','1206') ) m
- inner join gy_inoutsub s on m.inoutmainid=s.inoutmainid
- left join(
- select sms.mnumber,sm.whcode,sum(sms.quan) quan from Kf_StartMain sm
- inner join Kf_Startsub sms on sm.startmainid=sms.startmainid
- group by sm.whcode,sms.mnumber
- ) ss on m.whcode=ss.whcode and s.mnumber=ss.mnumber
- group by isnull(m.whcode,ss.whcode),isnull(s.mnumber,ss.mnumber) order by s.mnumber
上面將收發(fā)表的數(shù)量進(jìn)行匯總，然后再加上期初表的數(shù)量，得到庫存量。但是得到的實(shí)際數(shù)量卻多出很多來，比如本來物料“010101004”只有20噸，統(tǒng)計(jì)的結(jié)果卻有5000多噸。問題出在哪里呢？
- select sms.mnumber,sm.whcode,sum(sms.quan) quan from Kf_StartMain sm
- inner join Kf_Startsub sms on sm.startmainid=sms.startmainid
- where sms.mnumber='010101004 '
- group by sm.whcode,sms.mnumber
上面sql統(tǒng)計(jì)[期初表] 數(shù)量 = 31.500000
- select s.mnumber,m.whcode,
- isnull(sum(factreceiptquan),0)-isnull(sum(factissuequan),0) quan
- from (select * from gy_inoutmain where billcode in('1201','1202','1203','1204','1205','1206') ) m
- inner join gy_inoutsub s on m.inoutmainid=s.inoutmainid
- where s.mnumber='010101004 '
- group by m.whcode,s.mnumber
- order by s.mnumber
上面sql統(tǒng)計(jì)[收發(fā)表] 數(shù)量 = -27.000000
- select s.mnumber,m.whcode,
- isnull(sum(factreceiptquan),0)-isnull(sum(factissuequan),0) +sum(ss.quan) quan
- from (select * from gy_inoutmain where billcode in('1201','1202','1203','1204','1205','1206') ) m
- inner join gy_inoutsub s on m.inoutmainid=s.inoutmainid
- left join(
- select sms.mnumber,sm.whcode,sum(sms.quan) quan from Kf_StartMain sm
- inner join Kf_Startsub sms on sm.startmainid=sms.startmainid
- where sms.mnumber='010101004 '
- group by sm.whcode,sms.mnumber
- ) ss on m.whcode=ss.whcode and s.mnumber=ss.mnumber
- where s.mnumber='010101004 '
- group by m.whcode,s.mnumber
- order by s.mnumber
上面sql關(guān)聯(lián)兩表，數(shù)量=57145.500000，原來，在[收發(fā)表]關(guān)聯(lián)[期初表]，[收發(fā)表]有幾百條記錄，而[期初表]只有一條記錄，兩者一關(guān)聯(lián)，則這幾百條記錄都有期初數(shù)，結(jié)果期初數(shù)被累加了幾百次。
舉個(gè)例子：
- create table [期初表](MNUmber varchar(10),quan decimal(10,3))
- create table [收發(fā)表](MNUmber varchar(10),quan decimal(10,3))
- insert into [期初表] values('001',7)
- insert into [期初表] values('001',5)
- insert into [期初表] values('001',-9)
- insert into [收發(fā)表] values('001',10)
- select sum(quan) from 期初表 --期初表合計(jì)=3
那得到的現(xiàn)存量應(yīng)該是 [收發(fā)表]的合計(jì) 加上 期初數(shù)：3+10 = 13。但一關(guān)聯(lián)，再匯總，就出問題了。
- select sum(m.Quan)+sum(s.Quan) from [收發(fā)表] m
- inner join [期初表] s on m.MNumber=s.MNumber
- group by m.MNumber
得到的結(jié)果是33。因?yàn)橐魂P(guān)聯(lián)，就變成：
- 收發(fā)表.MNumber 收發(fā)表.quan 期初表.MNumber 期初表.quan
- ---------------------------------------------------------------------
- 001 7 001 10
- 001 5 001 10
- 001 -9 001 10
結(jié)果就變成：7+5+(-9)+10+10+10，期初數(shù)被加了三次。解決的辦法就是采用平級(jí)匯總的方式，先匯總，然后再關(guān)聯(lián)。不要關(guān)聯(lián)的兩邊，一邊是明細(xì)，一邊是匯總，那關(guān)聯(lián)肯定出問題。
關(guān)于SQL Server數(shù)據(jù)庫多表關(guān)聯(lián)匯總查詢的問題就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
【編輯推薦】
