T-SQL-多个WITH子句，然后是SUM总和

Question

我正在使用T-SQL，目的是使用三个WITH子句收集到我的主表查询中。如果我自己运行每个WITH查询，我可以得到所需的结果。但是，当将它混合到我的主表查询中时，不会显示任何结果。我认为我的WITH子句还可以，但对我的主表查询进行了篡改，没有得到想要的结果
任何帮助都是值得的

WITH n1 AS (SELECT m.name AS n1nom, SUM(y.Column1) AS SomeTotal1
FROM Mees m
INNER JOIN Listy y
ON y.m1=m.name
WHERE Yr=2020
GROUP BY m.name)
,
n2 AS (SELECT m.name AS n2nom, SUM(y.Column1) AS SomeTotal2
FROM Mees m
INNER JOIN Listy y
ON y.m2=m.name
WHERE Yr=2020
GROUP BY m.name)
,
n3 AS (SELECT m.name AS n3nom, SUM(y.Column1) AS SomeTotal3
FROM Mees m
INNER JOIN Listy y
ON y.m3=m.name
WHERE Yr=2020
GROUP BY m.name)
SELECT m.name, SUM(n1.sometotal1 + n2.sometotal2 + n3.sometotal3) AS Cool
FROM Mees M
INNER JOIN n1
ON n1.n1nom=m.name
INNER JOIN n2
ON n2.n2nom=m.name
INNER JOIN n3
ON n3.n3nom=m.name
GROUP BY m.name, n1.sometotal1 + n2.sometotal2 + n3.sometotal3;

Answer 1

如果在m.name与y.m1、y.m2和y.m3中的任何一个匹配时想要y.Column1的总数，则只需要1个联接和聚合：

SELECT m.name, SUM(y.Column1) AS Total
FROM Mees m
INNER JOIN Listy y
ON m.name IN (y.m1, y.m2, y.m3)
WHERE Yr = 2020
GROUP BY m.name

如果要多次添加y.Column1，以防y.m1、y.m2和y.m3中有多个与m.name匹配，则在SUM():中使用CASE表达式

SELECT m.name, 
SUM(
( 
CASE WHEN m.name = y.m1 THEN 1 ELSE 0 END +
CASE WHEN m.name = y.m2 THEN 1 ELSE 0 END +
CASE WHEN m.name = y.m3 THEN 1 ELSE 0 END
) * y.Column1
) AS Total
FROM Mees m
INNER JOIN Listy y
ON m.name IN (y.m1, y.m2, y.m3)
WHERE Yr = 2020
GROUP BY m.name