SELECT A.a1, C.c2, A.a2, C.c3,
(SELECT SUM(B.b3)
FROM A, B
WHERE A.a1 = B.b1
AND A.a1 = @ContractNo),
(SELECT SUM(D.d4)
FROM C, D,A
WHERE C.c1 = D.d1
AND C.c2 = D.d3
AND A.a1 = C.c1
AND A.a1 = @ContractNo),
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
(SELECT SUM(B.b3)
FROM A, B
WHERE A.a1 = B.b1
AND A.a1 = @ContractNo),
(SELECT SUM(D.d4)
FROM C, D,A
WHERE C.c1 = D.d1
AND C.c2 = D.d3
AND A.a1 = C.c1
AND A.a1 = @ContractNo),
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
(SELECT SUM(B.b3)
FROM A, B
WHERE A.a1 = B.b1
AND A.a1 = @ContractNo),
(SELECT SUM(D.d4)
FROM C, D
WHERE C.c1 = D.d1
AND C.c2 = D.d3
AND C.c1 = @ContractNo),
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
(SELECT SUM(B.b3)
FROM A, B
WHERE A.a1 = B.b1
AND A.a1 = @ContractNo),
tem.指示数
FROM A, C,(SELECT c1,SUM(D.d4) 指示数
FROM C, D
WHERE C.c1 = D.d1
AND C.c2 = D.d3) tem
WHERE A.a1 = C.c1 and tem.c1=c.c1
AND A.a1 = @ContractNo
FROM C, D
WHERE C.c1 = D.d1
AND C.c2 = D.d3),这里除了必要的关联,啥条件也没有,不是所有是什么?
(SELECT SUM(b3)
FROM B
WHERE b1=a.a1
),
(SELECT SUM(d4)
FROM D
WHERE d1=a.a1
)
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
SELECT A.a1, C.c2, A.a2, C.c3,
(SELECT SUM(B.b3)
FROM A, B
WHERE A.a1 = B.b1
AND A.a1 = @ContractNo),
(SELECT SUM(D.d4)
FROM C tem, D
WHERE tem.c1 = D.d1
AND tem.c2 = D.d3 and tem.c1=c.c1),
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
FROM C, D
WHERE C.c1 = D.d1
AND C.c2 = D.d3),这是一个子查询,跟外面的from A,C一点关系也没有
,又没有加入@ContractNo条件,当然会SUM出所有的来
请问CABO(白椒腊肉) 兄,
(SELECT SUM(D.d4)
FROM C, D
WHERE C.c1 = D.d1
AND C.c2 = D.d3
AND C.c1 = @ContractNo),
中最后一句,我曾用 AND D.d1 = @ContractNo) 但是结果还是SUM的,
这两句难道不一样吗?
或:
SELECT A.a1, C.c2, A.a2, C.c3,
(SELECT SUM(B.b3)
FROM B
WHERE B.b1=a.a1),
(SELECT SUM(D.d4)
FROM C tem, D
WHERE tem.c1 = D.d1
AND tem.c2 = D.d3 and tem.c1=c.c1),
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
(SELECT SUM(D.d4)
FROM D
WHERE D.d1 = @ContractNo),
应该这样就可以了,不用关联C表,试试
(SELECT SUM(B.b3)
FROM B
WHERE B.b1=a.a1),
(SELECT SUM(D.d4)
FROM C tem, D
WHERE tem.c1 = D.d1
AND tem.c2 = D.d3 and tem.c1=c.c1),
-------------------------------^^^^^^^^^^^^子查寻与父查询的关系
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
SELECT A.a1, C.c2, A.a2, C.c3,
(SELECT SUM(B.b3)
FROM B
WHERE B.b1 = @ContractNo),
(SELECT SUM(D.d4)
FROM D
WHERE D.d1 =@ContractNo ),
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo
(SELECT SUM(b3)
FROM B
WHERE b1=a.a1
),
(SELECT SUM(D.d4)
FROM D
WHERE c.c1 = D.d1
AND c.c2 = D.d3)
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo楼主试了吗????
大力兄,你前面写的
SELECT A.a1, C.c2, A.a2, C.c3,
(SELECT SUM(B.b3)
FROM B
WHERE B.b1=a.a1),
(SELECT SUM(D.d4)
FROM C tem, D
WHERE tem.c1 = D.d1
AND tem.c2 = D.d3 and tem.c1=c.c1),
-------------------------------^^^^^^^^^^^^子查寻与父查询的关系
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo是什么意思,我这么试了但是好像不行啊?
SELECT A.a1, C.c2, A.a2, C.c3,
(SELECT SUM(b3)
FROM B
WHERE b1=a.a1
),
(SELECT SUM(D.d4)
FROM D
WHERE c.c1 = D.d1
--------------^^^^^^^^^^^^子查寻与父查询的关系
AND c.c2 = D.d3)
--------------^^^^^^^^^^^^子查寻与父查询的关系
FROM A, C
WHERE A.a1 = C.c1
AND A.a1 = @ContractNo