时间戳

首页 > 解决方案 > 如何内部连接多个 sum/group 查询，每个查询都有不同的 where 子句？

sql - 如何内部连接多个 sum/group 查询，每个查询都有不同的 where 子句？

问题描述

在下面的代码上使用UNION会产生两列：

  1. [Operator Company Name]
  2. Sum(Gross Acres)

由于 5 个查询，每个[Operator Company Name]记录（行）有 5 个。

如何正确编写 a UNIONor JOINwhere 5 个查询中的每一个的结果都生成一个独立的列，然后将它们全部加入，[Operator Company Name]以便新查询输出为每列 1 条记录和 6 列（共同名称 + 5 个查询结果）

SELECT [Operator Company Name],
Sum([Area (Gross Acres)]) AS [Sum(Gross Acres)]
FROM Enervus_PrivateData
WHERE [US Region] Like 'Permian'
GROUP BY [Operator Company Name];

union

SELECT [Operator Company Name],
Sum([Area (Gross Acres)]) AS [Sum(TX DEL Acres)]
FROM Enervus_PrivateData
WHERE [US Region] Like 'Permian' AND [COUNTY/PARISH] Like '*Reeves*' OR [COUNTY/PARISH] Like '*Culberson*' OR [COUNTY/PARISH] Like '*Pecos*' OR [COUNTY/PARISH] Like '*Loving*' OR [COUNTY/PARISH] Like '*Ward*' OR [COUNTY/PARISH] Like '*Winkler*'
GROUP BY [Operator Company Name];

union

SELECT [Operator Company Name],
Sum([Area (Gross Acres)]) AS [Sum(N MID Acres)]
FROM Enervus_PrivateData
WHERE [US Region] Like 'Permian' AND [COUNTY/PARISH] Like '*Dawson*' OR [COUNTY/PARISH] Like '*Borden*' OR [COUNTY/PARISH] Like '*Martin*' OR [COUNTY/PARISH] Like '*Howard*'
GROUP BY [Operator Company Name];

union

SELECT [Operator Company Name],
Sum([Area (Gross Acres)]) AS [Sum(NM DEL Acres)]
FROM Enervus_PrivateData
WHERE [US Region] Like 'Permian' AND [COUNTY/PARISH] Like '*Eddy*' OR [COUNTY/PARISH] Like '*Lea*'
GROUP BY [Operator Company Name];

union

SELECT [Operator Company Name],
Sum([Area (Gross Acres)]) AS [Sum(S MID Acres)]
FROM Enervus_PrivateData
WHERE [US Region] Like 'Permian' AND [COUNTY/PARISH] Like '*Midland*' OR [COUNTY/PARISH] Like '*Glasscock*' OR [COUNTY/PARISH] Like '*Upton*' OR [COUNTY/PARISH] Like '*Reagan*'
GROUP BY [Operator Company Name];

实际结果应包括每列 1 条记录与列

  1. [Operator Company Name]- 所有查询都由
  2. [Sum(Gross Acres)]
  3. [Sum(TX DEL Acres)]
  4. [Sum(N MID Acres)]
  5. [Sum(NM DEL Acres)]
  6. [Sum(S MID Acres)]

标签： sqlms-access

解决方案

您应该能够使用条件聚合来解决这个问题。

诀窍在于在IIF每个语句中使用一个语句，该语句SUM()包含来自相应原始子查询的条件。如果满足条件，则应在 中考虑该值SUM，否则应忽略该值。

考虑：

SELECT 
    [Operator Company Name],
    Sum([Area (Gross Acres)]) AS [Sum(Gross Acres)],
    Sum(
        IIF( 
            (
                [COUNTY/PARISH] Like '*Reeves*' 
                OR [COUNTY/PARISH] Like '*Culberson*' 
                OR [COUNTY/PARISH] Like '*Pecos*' 
                OR [COUNTY/PARISH] Like '*Loving*' 
                OR [COUNTY/PARISH] Like '*Ward*' 
                OR [COUNTY/PARISH] Like '*Winkler*'
            ), 
            [Area (Gross Acres)],
            0
        )
    ) AS [Sum(TX DEL Acres)],
    Sum(
        IIF(
            (
                [COUNTY/PARISH] Like '*Dawson*' 
                OR [COUNTY/PARISH] Like '*Borden*' 
                OR [COUNTY/PARISH] Like '*Martin*' 
                OR [COUNTY/PARISH] Like '*Howard*'
            ),
            [Area (Gross Acres)],
            0
        )
    ) AS [Sum(N MID Acres)],
    Sum(
        IIF(
            (
                [COUNTY/PARISH] Like '*Eddy*' 
                OR [COUNTY/PARISH] Like '*Lea*'
            ),
            [Area (Gross Acres)],
            0
        )
    ) AS [Sum(NM DEL Acres)],
    Sum(
        IIF(
            (
                [COUNTY/PARISH] Like '*Midland*' 
                OR [COUNTY/PARISH] Like '*Glasscock*' 
                OR [COUNTY/PARISH] Like '*Upton*' 
                OR [COUNTY/PARISH] Like '*Reagan*'
            ),
            [Area (Gross Acres)],
            0
        )
    ) AS [Sum(S MID Acres)]
FROM Enervus_PrivateData
WHERE [US Region] Like 'Permian'
GROUP BY [Operator Company Name];

推荐阅读