【樓主】hellopala2012-11-16 14:46
» 有兩個(gè)表，暫定名稱(chēng)為A,B吧A表里儲(chǔ)存的字段有ID和數(shù)量，B表里也是ID和數(shù)量假如A表的數(shù)據(jù)下面這樣：ID NUM1 502 653 704 45B表中的數(shù)據(jù)是這樣ID1 NUM1a 45b 60c 30d 60e 35如何寫(xiě)語(yǔ)句能完成如下的效果：ID NUM ID1 NUM11 50 a 451 50 b 52 65 b 552 65 c 103 70 c 203 70 d 504 45 d 104 45 e 35就是用B表中的數(shù)量去填充A表的數(shù)量，B表數(shù)量不夠的就借下一行，夠的就再用剩余去填充A表的下一行數(shù)據(jù)
作者：混亂之水2012-11-16 14:54
看起來(lái)很復(fù)雜啊，你還是弄個(gè)存儲(chǔ)過(guò)程吧。
作者：梅友乾2012-11-16 15:08
看了半天樓主的示例沒(méi)明白最終目的是什么。感覺(jué)像是普通的笛卡爾積？那就直接SELECT A.ID, A.NUM, B.ID AS ID1, B.NUM AS NUM1 FROM A CROSS JOIN B應(yīng)該就行吧？這倆表沒(méi)啥關(guān)系，提到連接這事很抽象
作者：hellopala2012-11-16 15:08
建立中間表么？先求出結(jié)果中id1和num1的樣子然后再去和表a對(duì)應(yīng)？-------發(fā)自后頭丑(HTC) 7 Mozart T8698上的
微民網(wǎng)Brush for WP7
作者：hellopala2012-11-16 15:14
看了半天樓主的示例沒(méi)明白最終目的是什么。感覺(jué)像是普通的笛卡爾積？那就直接SELECT A.ID, A.NUM, B.ID AS ID1, B.NUM AS NUM1 FROM A CROSS JOIN B應(yīng)該就行吧？這倆表沒(méi)啥關(guān)系，提到連接這事很抽象
作者：arale0072012-11-16 15:37
ORACLE 10G以上的寫(xiě)法--START FOR TEST DATAWITH A AS(SELECT 1 AS ID ,50 AS NUM FROM DUALUNION ALL SELECT 2 AS ID ,65 AS NUM FROM DUALUNION ALLSELECT 3 AS ID ,70 AS NUM FROM DUALUNION ALLSELECT 4 AS ID ,45 AS NUM FROM DUAL),B AS (SELECT 'a' AS ID1,45 AS NUM1 FROM DUALUNION ALLSELECT 'b' AS ID1,60 AS NUM1 FROM DUALUNION ALLSELECT 'c' AS ID1,30 AS NUM1 FROM DUALUNION ALLSELECT 'd' AS ID1,60 AS NUM1 FROM DUALUNION ALLSELECT 'e' AS ID1,35 AS NUM1 FROM DUAL)--END FOR TEST DATASELECT AA.ID,AA.NUM,BB.ID1 ,CASE WHEN AA.NUMM BETWEEN BB.NUMM AND BB.NUMM + BB.NUM1 THEN BB.NUMM + BB.NUM1 - AA.NUMM ELSE AA.NUMM + AA.NUM - BB.NUMM END AS NUM1FROM (SELECT A.ID,A.NUM,NVL(SUM(A.NUM) OVER(ORDER BY A.ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS NUMM FROM A) AALEFT JOIN (SELECT B.ID1,B.NUM1,NVL(SUM(B.NUM1) OVER(ORDER BY B.ID1 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS NUMM FROM B) BBON AA.NUMM BETWEEN BB.NUMM AND BB.NUMM + BB.NUM1OR AA.NUMM + AA.NUM BETWEEN BB.NUMM AND BB.NUMM + BB.NUM1結(jié)果ID NUM ID1 NUM11 50 a 451 50 b 52 65 b 552 65 c 103 70 c 203 70 d 504 45 d 104 45 e 35其它的數(shù)據(jù)庫(kù)寫(xiě)起來(lái)太累，而且效率不保證。
作者：爬墻頭2012-11-16 15:41
有點(diǎn)難度，樓主你還是寫(xiě)過(guò)程或者java做吧即便寫(xiě)出來(lái)這樣的sql，維護(hù)的人看到了也會(huì)罵的。
作者：爬墻頭2012-11-16 15:45
你這個(gè)已經(jīng)涉及到行和行的計(jì)算，而且還有順序的事，以我愚鈍的大腦，又想了想，不太好整。
作者：炎殤2012-11-16 15:46
好吧，我看懂了改動(dòng)
作者：believekurt2012-11-16 15:48
兩個(gè)表沒(méi)有一個(gè)屬性是可以把她們聯(lián)系起來(lái)的在弄一個(gè)表吧...
作者：thorhero2012-11-16 15:50
終于看懂了，這個(gè)東西只可能用存儲(chǔ)過(guò)程搞吧，而且我覺(jué)得用存儲(chǔ)過(guò)程搞也不科學(xué)，明顯應(yīng)該程序算了再放到數(shù)據(jù)庫(kù)里。5樓那個(gè)。。沒(méi)通用性吧，樓主只是舉了個(gè)例。
作者：hellopala2012-11-16 15:52
Post by arale007 (2012-11-16 15:37) Post by thorhero (2012-11-16 15:50) Post by arale007 (2012-11-16 15:55) 。。。你這一串就只為把LZ那2個(gè)表拼合起來(lái)？按我理解LZ只是舉了個(gè)例子問(wèn)怎么拼合這一類(lèi)的兩個(gè)表。難到不是？?jī)杀矸謩e處理一下后就可以寫(xiě)出關(guān)聯(lián)條件，有什么問(wèn)題？
作者：飛揚(yáng)的塵埃2012-11-16 17:25
如果把A值作為大循環(huán)，B值填充動(dòng)作作為小循環(huán)，那么B值累計(jì)需要用到函數(shù)迭代，這個(gè)貌似SQL做不了。Declare @MA int, @MB int, @AID int, @A int, @BID nchar(1), @B intDeclare CurA Cursor Read_Only For Select * From ADeclare CurB Cursor Read_Only For Select * From BOpen CurAOpen CurBFetch Next From CurA Into @AID, @AIf @@Fetch_Status = 0 Begin Fetch Next From CurB Into @BID, @BEndSet @MA=0, @MB=0While @@Fetch_Status = 0 Begin -- 標(biāo)簽一 If @A+@MA >= @B+@MB Begin Insert Into dbo.C Values(@AID,@A,@BID,@B) Set @MA=@A-@B, @MB=0 Set @A=0, @B=0 Fetch Next From CurB Into @BID, @B If @@Fetch_Status <> 0 Break --B值已取完 -- 執(zhí)行迭代，即回到標(biāo)簽一處 End Else Begin If @MB>0 Begin Insert Into dbo.C Values(@AID,@A,@BID,@MB) Insert Into dbo.C Values(@AID,@A,@BID,@A-@MB) End Else Begin Insert Into dbo.C Values(@AID,@A,@BID,@A) End Set @MA=0,@MB=@B+@MB-@A Set @A=0, @B=0 End Fetch Next From CurAEndClose CurADeallocate CurAClose CurBDeallocate CurB
