污网站视频在线观看,亚洲国产婷婷综合在线精品中文,国产精品欧美性在线观看,久久久久久大鸡吧大精品久久久久久

» 有兩個表，暫定名稱為A,B吧A表里儲存的字段有ID和數(shù)量，B表里也是ID和數(shù)量假如A表的數(shù)據(jù)下面這樣：ID NUM1 502 653 704 45B表中的數(shù)據(jù)是這樣ID1 NUM1a 45b 60c 30d 60e 35如何寫語句能完成如下的效果：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ù)看起來很復(fù)雜啊，你還是弄個存儲過程吧。看了半天樓主的示例沒明白最終目的是什么。感覺像是普通的笛卡爾積？那就直接SELECT A.ID, A.NUM, B.ID AS ID1, B.NUM AS NUM1 FROM A CROSS JOIN B應(yīng)該就行吧？這倆表沒啥關(guān)系，提到連接這事很抽象建立中間表么？先求出結(jié)果中id1和num1的樣子然后再去和表a對應(yīng)？-------發(fā)自后頭丑(HTC) 7 Mozart T8698上的微民網(wǎng)Brush for WP7看了半天樓主的示例沒明白最終目的是什么。感覺像是普通的笛卡爾積？那就直接SELECT A.ID, A.NUM, B.ID AS ID1, B.NUM AS NUM1 FROM A CROSS JOIN B應(yīng)該就行吧？這倆表沒啥關(guān)系，提到連接這事很抽象ORACLE 10G以上的寫法--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ù)庫寫起來太累，而且效率不保證。有點難度，樓主你還是寫過程或者java做吧即便寫出來這樣的sql，維護的人看到了也會罵的。你這個已經(jīng)涉及到行和行的計算，而且還有順序的事，以我愚鈍的大腦，又想了想，不太好整。好吧，我看懂了改動兩個表沒有一個屬性是可以把她們聯(lián)系起來的在弄一個表吧...終于看懂了，這個東西只可能用存儲過程搞吧，而且我覺得用存儲過程搞也不科學(xué)，明顯應(yīng)該程序算了再放到數(shù)據(jù)庫里。5樓那個。。沒通用性吧，樓主只是舉了個例。 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個表拼合起來？按我理解LZ只是舉了個例子問怎么拼合這一類的兩個表。難到不是？兩表分別處理一下后就可以寫出關(guān)聯(lián)條件，有什么問題？如果把A值作為大循環(huán)，B值填充動作作為小循環(huán)，那么B值累計需要用到函數(shù)迭代，這個貌似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