sql语句的问题

[六月初八]

总共就10分了，麻烦高人帮忙看下，谢谢了

表A
id,num,ck_id
a1 10 c1
a2 11 c1
a3 12 c1
a1 20 c2
a1 39 c1
a2 10 c1

表B
id,num,ck_id
a1 10 C1
a2 10 C1
a2 11 C1
a3 13 C1
a1 9 C2

表C
id name ck_id
a1 P1 C1
a1 P1 C2
a2 P2 C1
a3 P3 C1

表D
ck_id ck
C1 材料库
C2 成品库

要求结果
输出结果：
id name num ck_id ck
a1 P1 59 c1 材料库
a1 P1 29 c2 成品库
a2 p2 42 c1 材料库
a3 p3 25 c1 材料库

表A\B是要统计相加的二表（求二表对应ID的NUM之和,如上面的结果
需要注意的是，例子中a1 既属于c1库也属于c2库，统计时需要先以id区分再以ck_id区分）

表C 货物名称及对应的仓库ID(同名同姓的货物可能在其它仓库)
表D 是仓库ID对应的仓库名称

需要注意的是，例子中a1 既属于c1库也属于c2库，统计时需要先以id区分再以ck_id区分

select t1.id,t1.ck_id,sum(DISTINCT t1.记录数)as 记录数2 from (SELECT id, ck_id, SUM(DISTINCT num) AS 记录数
FROM dbo.表A
GROUP BY id, ck_id
UNION ALL
SELECT id, ck_id,SUM(DISTINCT num) AS 记录数
FROM dbo.表B
GROUP BY id, ck_id) as t1)GROUP BY t1.id,t1.ck_id
以SQL统计出id ck_id num，如何将ck,name 通过另二个表关联进去，我关联后会导致，相同ID不同仓库的出现多少重复记录

[六月初八]

补充一下，少了字段 表C id name ck_id istrue a1 P1 C1 true a1 P1 C2 false a2 P2 C1 true a3 P3 C1 true 二个a1的istrue值不一样，Istrue也要在最后的表中显示出来，关键是这条加上去，就会导致数据重复了

[Shawn]

;WITH cte AS
(
	select t1.id,t1.ck_id,sum(t1.记录数) as 记录数2 
	from 
	(
		SELECT id, ck_id, SUM(num) AS 记录数
		FROM dbo.表A
		GROUP BY id, ck_id
		UNION ALL
		SELECT id, ck_id,SUM(num) AS 记录数
		FROM dbo.表B
		GROUP BY id, ck_id
	) as t1
	GROUP BY t1.id,t1.ck_id
)
SELECT a.id,c.NAME,a.num,a.ck_id,ck=d.ck
FROM cte a
INNER JOIN 表D d
	ON a.ck_id = d.ck_id
CROSS APPLY
(SELECT TOP(1) * FROM 表C m WHERE m.id = a.id AND m.ck_id=a.ck_id ORDER BY istrue desc) c