现在有 A，B，C，D四张表，A为主表，B、C、D都是子表，与A属于一对多关系。查询后出现大量重复数据
表A
-----------------------------------------------
cID Name
1 张三
2 李四
表B
-----------------------------------------------
cID Car
1 本田飞度
1 POLO
表C
-----------------------------------------------
cID Credit
1 广发信用卡
1 兴业信用卡
1 民生信用卡
表D
-----------------------------------------------
cID Hose
1 怡海花园14号楼201
1 富锦家园7号楼702
查询语句
SELECT * FROM A LEFT JOIN B ON (A.cID = B.cID) LEFT JOIN C ON (A.cID = C.cID) LEFT JOIN D ON (A.cID = D.cID)
查询结果为
cID Name Car Credit Hose
1 张三 本田飞度 广发信用卡 怡海花园14号楼201
1 张三 本田飞度 广发信用卡 富锦家园7号楼702
1 张三 本田飞度 兴业信用卡 怡海花园14号楼201
1 张三 本田飞度 兴业信用卡 富锦家园7号楼702
1 张三 本田飞度 民生信用卡 怡海花园14号楼201
1 张三 本田飞度 民生信用卡 富锦家园7号楼702
1 张三 POLO 广发信用卡 怡海花园14号楼201
1 张三 POLO 广发信用卡 富锦家园7号楼702
1 张三 POLO 兴业信用卡 怡海花园14号楼201
1 张三 POLO 兴业信用卡 富锦家园7号楼702
1 张三 POLO 民生信用卡 怡海花园14号楼201
1 张三 POLO 民生信用卡 富锦家园7号楼702
我最终想实现的效果为：
cID Name Car Credit Hose
1 张三 本田飞度 广发信用卡 怡海花园14号楼201
POLO 兴业信用卡 富锦家园7号楼702
民生信用卡
------解决思路----------------------
---1楼的 将LEFT JOIN 修改成INNER JOIN 就正确了
;WITH BCte AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM B
)
,CCte AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM C
)
,DCte AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,* FROM D
)
SELECT A.*,T1.Car,T2.Credit,T3.Hose FROM A
INNER JOIN BCte T1 ON A.cID=T1.cID
FULL JOIN CCte T2 ON A.cID=T2.cID AND T1.RN=T2.RN
FULL JOIN DCte T3 ON A.cID=T3.cID AND T1.RN=T3.RN
------解决思路----------------------
同cID的记录数小于2048有效
;WITH ACte AS(
SELECT TB2.number RN,TB1.* FROM A TB1,master..spt_values TB2 WHERE TB2.type='P'
)
,BCte AS(
SELECT ROW_NUMBER()OVER(PARTITION BY cID ORDER BY GETDATE())RN,* FROM B
)
,CCte AS(
SELECT ROW_NUMBER()OVER(PARTITION BY cID ORDER BY GETDATE())RN,* FROM C
)
,DCte AS(
SELECT ROW_NUMBER()OVER(PARTITION BY cID ORDER BY GETDATE())RN,* FROM D
)
SELECT T.cID,T.NAME,T1.Car,T2.Credit,T3.Hose FROM ACte T
LEFT JOIN BCte T1 ON T.cID=T1.cID AND T.RN=T1.RN
FULL JOIN CCte T2 ON T.cID=T2.cID AND T.RN=T2.RN
FULL JOIN DCte T3 ON T.cID=T3.cID AND T.RN=T3.RN
WHERE (T1.cID IS NOT NULL OR T2.cID IS NOT NULL OR T3.cID IS NOT NULL)
ORDER BY T.cID,T.RN