我在做INNER JOIN 连接的时候不能实现对B表的汇总,
如:
A表中同一个ID(非主索引)有重复的记录。
B表中的ID(非主索引)。
SELECT SUM(B。NUM) AS N FORM A INNER JOIN B ON A。ID=B。ID GROUP BY A。ID
那么B表中的NUM就会根据A表中的记录数量乘以真正的SUM(B。NUM)的结果。
举例:
A表
ID NAME KC
01 BASIC 10
01 BASIC 11
01 BASIC 2
B表
ID NUM Tdate
01 20
通过
SELECT SUM(B。NUM) AS N FORM A INNER JOIN B ON A。ID=B。ID where...
结果就是 sum(num) 60 应该为20才对啊
如:
A表中同一个ID(非主索引)有重复的记录。
B表中的ID(非主索引)。
SELECT SUM(B。NUM) AS N FORM A INNER JOIN B ON A。ID=B。ID GROUP BY A。ID
那么B表中的NUM就会根据A表中的记录数量乘以真正的SUM(B。NUM)的结果。
举例:
A表
ID NAME KC
01 BASIC 10
01 BASIC 11
01 BASIC 2
B表
ID NUM Tdate
01 20
通过
SELECT SUM(B。NUM) AS N FORM A INNER JOIN B ON A。ID=B。ID where...
结果就是 sum(num) 60 应该为20才对啊
select SUM(B.NUM) FROM B如果非要连表,那就是说你还要用A表里的东西,否则连表也没有意义。那么你可以把A表GROUP起来,建一个能够使ID唯一的临时视图,再用B表去连这个视图。Select C.ID,sum(B.NUM) from B ,(SELECT A.ID AS ID,MAX(A.NAME) AS NAME
,SUM(A.KC) AS KC from A GROUP BY A.ID) AS C
where B.ID=C.ID
group by C.ID
(SELECT DISTINCT A.id
FROM A) c ON B.id = c.id
GROUP BY B.id
Select C.ID,sum(B.NUM) from B ,(SELECT A.ID AS ID,MAX(A.NAME) AS NAME
,SUM(A.KC) AS KC from A GROUP BY A.ID) AS C
where B.ID=C.ID
group by C.ID 我测试了.
但是如果在临时表C中做了SUM(A.KC) AS KC 那么在SELECT C。KC,或SUM(A。KC)在第一个SELECT中就不能实现
一旦B。ID非主索引,
A。ID非主索引。
那么做连接就一定要产生笛卡儿乘积属性
,SUM(A.KC) AS KC from A GROUP BY A.ID) AS C
where B.ID=C.ID
group by C.ID ,C.KC