总共就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不同仓库的出现多少重复记录
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也要在最后的表中显示出来,关键是这条加上去,就会导致数据重复了
(
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