sql - 如何从 union 语句中删除重复行

Question

好的 - 我看了又看，发现了很多例子，但没有什么完全满足我的需要。也许我用了错误的词来搜索，但我需要你的帮助。我将尽可能提供详细信息。

我需要生成一份报告，将两个表(或者更确切地说一个 View 和一个表)中的字段合并到一个报告表中。这是我正在使用的声明:

SELECT A.ConfInt, A.Conference, 
        NULL as Ordered,
        NULL as Approved,
        NULL as PickedUp,
        SUM(dbo.Case_Visit_Payments.Qty) AS Qty
FROM         dbo.Conferences as A INNER JOIN
                      dbo.Case_Table ON A.ConfInt = dbo.Case_Table.Conference_ID INNER JOIN
                      dbo.Case_Visit_Payments ON dbo.Case_Table.Case_ID = dbo.Case_Visit_Payments.Case_ID
WHERE     (dbo.Case_Visit_Payments.Item_ID = 15 AND A.ProjectCool = 1)
GROUP BY A.Conference, A.ConfInt
UNION
SELECT  B.ConfInt, 
        B.Conference, 
        SUM(dbo.Cool_Fan_Order.NumberOfFansRequested) AS Ordered, 
        SUM(dbo.Cool_Fan_Order.Qty_Fans_Approved) AS Approved, 
        SUM(dbo.Cool_Fan_Order.Qty_Fans_PickedUp) AS PickedUp, 
        NULL AS Qty
FROM         dbo.Conferences as B LEFT OUTER JOIN
                      dbo.Cool_Fan_Order ON B.ConfInt = dbo.Cool_Fan_Order.Conference_ID
where B.ProjectCool = 1
GROUP BY B.Conference, B.ConfInt

结果如下:

4   Our Lady        NULL    NULL    NULL    11
4   Our Lady        40      40      40      NULL
7   Holy Rosary     20      20      20      NULL
11  Little Flower   NULL    NULL    NULL    21
11  Little Flower   5       5       20      NULL
19  Perpetual Help  NULL    NULL    NULL    2
19  Perpetual Help  20      20      20      NULL

我强烈希望没有重复的行，例如:

4   Our Lady        40      40      40      11
7   Holy Rosary     20      20      20      NULL
11  Little Flower   5       5       20      21
19  Perpetual Help  20      20      20      2

我希望这个问题足够清楚。任何建议将不胜感激。我确实标记为已回答。 :)

格雷戈里

Answer 1

您可以使用实际查询作为子查询，对非重复值使用聚合函数(MAX OR SUM)并按非聚合列进行分组

SELECT ConfInt, Conference, MAX(Ordered), MAX(Approved), MAX(PickedUp), MAX(Qty)
FROM (<your actualQuery>)
GROUP BY ConfInt, Conference.