join left on多张表_left join 联接多张一对多子表时出现重复数据，求大神给个解决方案...

left join 连接多张一对多子表时出现重复数据，求大神给个解决方案

现在有 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