就是LEFT JOIN B ON B.PADOCENTRY = A.DOCENTRY LEFT JOIN C ON C.PADOCENTRY = A.DOCENTRYSUM(QUANTITY)-SUM(QUANTITY)
--try use tempdb go create table A (DOCENTRY int ,DOCDATE varchar(10),U_QTY int) insert into A values (5263,'2010-9-8',10) insert into A values (5264,'2010-9-9',10) insert into A values (5265,'2010-9-10',20)create table B(DOCNUM int ,DOCDATE varchar(10),QUANTITY int ,PADOCENTRY int) insert into B values (7378,'2010-9-10',8,5263) insert into B values (7379,'2010-9-12',2,5263) insert into B values (7380,'2010-9-10',10,5264) insert into B values (7381,'2010-9-11',15,5265) insert into B values (7382,'2010-9-12',5,5265) create table C(DOCNUM int ,DOCDATE varchar(10),QUANTITY int ,PADOCENTRY int) insert into C values (30750,'2010-9-14',10,5263) insert into C values (330751,'2010-9-11',4,5264) insert into C values (330752,'2010-9-12',6,5264) insert into C values (330753,'2010-9-13',11,5265) insert into C values (330754,'2010-9-14',9,5265) ;with BB AS ( select B.PADOCENTRY,sum(B.QUANTITY) BQUANTITY from B group by B.PADOCENTRY ), CC AS ( select C.PADOCENTRY,sum(C.QUANTITY) CQUANTITY from C group by C.PADOCENTRY ) select A.DOCENTRY,A.DOCDATE,A.U_QTY,B.DOCDATE,B.QUANTITY,C.DOCDATE,C.QUANTITY,(BB.BQUANTITY-CC.CQUANTITY) PADOCENTRY from A JOIN B ON B.PADOCENTRY = A.DOCENTRY JOIN C ON C.PADOCENTRY = A.DOCENTRY JOIN BB ON B.PADOCENTRY = BB.PADOCENTRY JOIN CC ON C.PADOCENTRY = CC.PADOCENTRY
LEFT JOIN B ON B.PADOCENTRY = A.DOCENTRY LEFT JOIN C ON C.PADOCENTRY = A.DOCENTRY 连接了后,A.DOCENTRY,值5263、5264、5265,所关联的B.QUANTITY - C.QUANTITY,不是都等于0了么? 5263关联的B.QUANTITY 是10,C.QUANTITY也是10, 5264关联的B.QUANTITY 是10,C.QUANTITY也是10, 5265关联的B.QUANTITY 是20,C.QUANTITY也是20。 最后列是B.QUANTITY-C.QUANTITY,那么不就都是0了么
就是LEFT JOIN B ON B.PADOCENTRY = A.DOCENTRY
LEFT JOIN C ON C.PADOCENTRY = A.DOCENTRYSUM(QUANTITY)-SUM(QUANTITY)
use tempdb
go
create table A (DOCENTRY int ,DOCDATE varchar(10),U_QTY int)
insert into A values (5263,'2010-9-8',10)
insert into A values (5264,'2010-9-9',10)
insert into A values (5265,'2010-9-10',20)create table B(DOCNUM int ,DOCDATE varchar(10),QUANTITY int ,PADOCENTRY int)
insert into B values (7378,'2010-9-10',8,5263)
insert into B values (7379,'2010-9-12',2,5263)
insert into B values (7380,'2010-9-10',10,5264)
insert into B values (7381,'2010-9-11',15,5265)
insert into B values (7382,'2010-9-12',5,5265) create table C(DOCNUM int ,DOCDATE varchar(10),QUANTITY int ,PADOCENTRY int)
insert into C values (30750,'2010-9-14',10,5263)
insert into C values (330751,'2010-9-11',4,5264)
insert into C values (330752,'2010-9-12',6,5264)
insert into C values (330753,'2010-9-13',11,5265)
insert into C values (330754,'2010-9-14',9,5265) ;with BB AS (
select B.PADOCENTRY,sum(B.QUANTITY) BQUANTITY
from B
group by B.PADOCENTRY
),
CC AS (
select C.PADOCENTRY,sum(C.QUANTITY) CQUANTITY
from C
group by C.PADOCENTRY
)
select A.DOCENTRY,A.DOCDATE,A.U_QTY,B.DOCDATE,B.QUANTITY,C.DOCDATE,C.QUANTITY,(BB.BQUANTITY-CC.CQUANTITY) PADOCENTRY
from A
JOIN B ON B.PADOCENTRY = A.DOCENTRY
JOIN C ON C.PADOCENTRY = A.DOCENTRY
JOIN BB ON B.PADOCENTRY = BB.PADOCENTRY
JOIN CC ON C.PADOCENTRY = CC.PADOCENTRY
--result:
DOCENTRY DOCDATE U_QTY DOCDATE QUANTITY DOCDATE QUANTITY PADOCENTRY
5263 2010-9-8 10 2010-9-10 8 2010-9-14 10 0
5263 2010-9-8 10 2010-9-12 2 2010-9-14 10 0
5264 2010-9-9 10 2010-9-10 10 2010-9-11 4 0
5264 2010-9-9 10 2010-9-10 10 2010-9-12 6 0
5265 2010-9-10 20 2010-9-11 15 2010-9-13 11 0
5265 2010-9-10 20 2010-9-11 15 2010-9-14 9 0
5265 2010-9-10 20 2010-9-12 5 2010-9-13 11 0
5265 2010-9-10 20 2010-9-12 5 2010-9-14 9 0
LEFT JOIN C ON C.PADOCENTRY = A.DOCENTRY
连接了后,A.DOCENTRY,值5263、5264、5265,所关联的B.QUANTITY - C.QUANTITY,不是都等于0了么?
5263关联的B.QUANTITY 是10,C.QUANTITY也是10,
5264关联的B.QUANTITY 是10,C.QUANTITY也是10,
5265关联的B.QUANTITY 是20,C.QUANTITY也是20。
最后列是B.QUANTITY-C.QUANTITY,那么不就都是0了么