» 有两个表，暂定名称为A,B吧A表里储存的字段有ID和数量，B表里也是ID和数量假如A表的数据下面这样：ID NUM1 502 653 704 45B表中的数据是这样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表中的数量去填充A表的数量，B表数量不够的就借下一行，够的就再用剩余去填充A表的下一行数据看起来很复杂啊，你还是弄个存储过程吧。看了半天楼主的示例没明白最终目的是什么。感觉像是普通的笛卡尔积？那就直接SELECT A.ID, A.NUM, B.ID AS ID1, B.NUM AS NUM1 FROM A CROSS JOIN B应该就行吧？这俩表没啥关系，提到连接这事很抽象建立中间表么？先求出结果中id1和num1的样子然后再去和表a对应？-------发自后头丑(HTC) 7 Mozart T8698上的微民网Brush for WP7看了半天楼主的示例没明白最终目的是什么。感觉像是普通的笛卡尔积？那就直接SELECT A.ID, A.NUM, B.ID AS ID1, B.NUM AS NUM1 FROM A CROSS JOIN B应该就行吧？这俩表没啥关系，提到连接这事很抽象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结果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其它的数据库写起来太累，而且效率不保证。有点难度，楼主你还是写过程或者java做吧即便写出来这样的sql，维护的人看到了也会骂的。你这个已经涉及到行和行的计算，而且还有顺序的事，以我愚钝的大脑，又想了想，不太好整。好吧，我看懂了改动两个表没有一个属性是可以把她们联系起来的在弄一个表吧...终于看懂了，这个东西只可能用存储过程搞吧，而且我觉得用存储过程搞也不科学，明显应该程序算了再放到数据库里。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只是举了个例子问怎么拼合这一类的两个表。难到不是？两表分别处理一下后就可以写出关联条件，有什么问题？如果把A值作为大循环，B值填充动作作为小循环，那么B值累计需要用到函数迭代，这个貌似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 -- 标签一 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值已取完 -- 执行迭代，即回到标签一处 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