返回充气总数的多个子表的总和

Question

TABLE1 - RegId (int) pk, RegDate (date), CityId, Tons1(int), Tons2(int)

TABLE2 - RegId(int) fk, OtherDate (date)

TABLE3 - id, City

    SELECT c.City
        , SUM(t1.Tons1 + t1.Tons2) as 'TotalTons'

    FROM Table1 t1
    JOIN Table2 t2 ON t1.RegId = t2.RegId
    JOIN table3 c ON t1.CityId = c.Id
    WHERE YEAR(t2.OtherDate) = '2016'
    GROUP BY c.City

如果表2中有多个匹配项，则“TotalTons”将加倍。如果删除联接，则可以，但必须为WHERE子句提供table2日期。

示例：

TABLE1
(20, '2016-2-2', 3, 2, 3)
(21, '2016-4-12', 7, 3, 5)
(22, '2016-4-12', 7, 6, 3)


TABLE 2
(20, '2016-2-2')
(20, '2016-2-3')
(20, '2016-2-5')
(20, '2016-2-1')
(21, '2016-5-12')
(22, '2016-9-2')


TABLE 3
(3, 'Dallas')
(7, 'Kansas')

结果

City    TotalTons
----    ---------
Dallas  20 (should be 5)
Kansas  17 (Correct)

所以达拉斯的总吨是5，但是由于RegId 20的表2中有5个不同的行，所以它乘以4倍。

如何使它停止将和乘以t2结果？

Answer 1

您可以用一个现有查询和一个子查询替换连接：

SELECT c.City
, SUM(t1.Tons1 + t1.Tons2) as 'TotalTons'
FROM Table1 t1
JOIN table3 c ON t1.CityId = c.Id
WHERE EXISTS (
     SELECT 1
     FROM Table2 t2
     WHERE t2.RegId = t1.RegId
     AND YEAR(t2.OtherDate) = '2016'
)
GROUP BY c.City

Answer 2

试试这个：

DECLARE @TABLE1 TABLE(RegId INT,RegDate DATE, CityId INT, Tons1 INT, Tons2 INT)
INSERT INTO @TABLE1 VALUES
(20, '2016-2-2', 3, 2, 3),(21, '2016-4-12', 7, 3, 5),(22, '2016-4-12', 7, 6, 3)

DECLARE @TABLE2 TABLE(RegId INT,OtherDate DATE)
INSERT INTO @TABLE2 VALUES (20, '2016-2-2'),(20, '2016-2-3'),(20, '2016-2-5'),
(20, '2016-2-1'),(21, '2016-5-12'),(22, '2016-9-2')

DECLARE @TABLE3 TABLE(id INT,City NVARCHAR(10))
INSERT INTO @TABLE3 VALUES (3, 'Dallas'),(7, 'Kansas')

SELECT DISTINCT B.City,B.TotalTons FROM ( SELECT T2.*,CityId FROM @TABLE2 T2 
LEFT JOIN @TABLE1 T1 ON T1.RegId = T2.RegId ) A LEFT JOIN (SELECT CityId,City, SUM(Tons1+Tons2) TotalTons 
FROM @TABLE3 T3 LEFT JOIN @TABLE1 T1 ON T1.CityId = T3.id GROUP BY CityId,City ) B ON A.CityId = B.CityId
WHERE YEAR(OtherDate) = '2016' -- change your date clause

希望能帮上忙。:)