询问者
三张表关联查询(三张表都有字段需要显示)

问题
全部回复
-
Hi,
您重覆發帖了哦!
http://social.msdn.microsoft.com/Forums/zh-TW/64ef219f-47b2-4aad-a9ae-e303d453b586
USE tempdb
GO
CREATE TABLE A
(
account INT
) ;
CREATE TABLE B
(
account INT
, custname VARCHAR(10)
) ;
CREATE TABLE C
(
cid INT
, custname VARCHAR(10)
, custnamecn VARCHAR(20)
);
INSERT INTO A VALUES(1), (2), (3);
INSERT INTO B VALUES(1, 'cname1'), (2, 'cname2'), (3, 'cname3');
INSERT INTO C VALUES(1, 'cname1', 'custnamecn1'), (2, 'cname2', 'custnamecn2'), (3, 'cname3', 'custnamecn3');
SELECT * from dbo.A
SELECT * from dbo.B
SELECT * from dbo.C
SELECT A.account, B.custname, C.custnamecn
from A INNER JOIN B ON A.account = B.account
INNER JOIN C ON B.custname = C.custname如果您有些Key無法過濾,可試著加入distinct!
SELECT DISTINCT A.account, B.custname, C.custnamecn
from A INNER JOIN B ON A.account = B.account
INNER JOIN C ON B.custname = C.custname -
Hi,
如果您依 A.account, B.custname, C.custnamecn Group的話,那應該也會變成不重覆的資料哦!
還是您可以建立一個簡單的測試Script,Po出來給大伙參考看看哦! Thanks.
-
-
Hi,
應該是因為B的資料中,有很多相同的account吧!
請將COUNT(A.account) 改成 COUNT( DISTINCT A.account) ,如下,
SELECT A.account ,
SUM(A.amount) AS totalamount ,
COUNT( DISTINCT A.account) AS count ,
B.custname ,
C.custnamecn
FROM A
INNER JOIN B ON A.account = B.account
INNER JOIN C ON B.custname = C.custname
GROUP BY A.account ,
B.custname ,
C.custnamecn -
后来我尝试了另一种写法,SELECT account,SUM(amount) as totalamount,COUNT(account) as count,C.custnamecn FROM (select DISTINCT A.account,A.amount,B.custname,C.custnamecn from A INNER JOIN B ON A.account = B.account INNER JOIN C ON B.custname = C.custname ) as t GROUP BY account,custnamecn 这个写法有个问题,就是如果account和amount完全一样的两条数据会合并为一条,这样计算出来的总数就少了一条记录。
-