按多列分组，聚集其他列，并在Server中选择all

Question

我使用的是SQL服务器，我似乎无法构造我想要的查询。我有一张有几列的桌子，其中包括

PARAMETER_NAME，GW_LOCATION_ID，Report_Result，DETECT_FLAG

我想要返回一个查询

1. 每个唯一参数-位置组合的一行
2. 唯一参数-位置组合的Report_Result列的最大值
3. 与最大值关联的DETECT_FLAG值。

除了DETECT_FLAG之外，我还想返回其他列，但是我认为，如果我能够完成这个部分，我应该能够返回其他列。

如果我按PARAMETER_NAME和GW_LOCATION_ID分组并聚合Report_Result列(参见下面)，则查询可以工作。但是，当我添加DETECT_FLAG列时，我会得到一个错误：“SLVs_Flagged.DETECT_FLAG列在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。”我想要的是MAX(Report_Result)返回的行的Report_Result值。

SELECT 
PARAMETER_NAME, GW_LOCATION_ID, MAX(Report_Result)
FROM SLVs_Flagged
GROUP BY PARAMETER_NAME, GW_LOCATION_ID
ORDER BY PARAMETER_NAME, GW_LOCATION_ID;

我尝试做一个子选择来返回对应于MAX(Report_Result)的Report_Result值，但是我尝试使用WHERE条件，并得到另一个错误。请告诉我如何执行这个查询。

下面是用于测试的数据子集。

PARAMETER_NAME  GW_LOCATION_ID  Report_Result   DETECT_FLAG
Perchlorate CDBO-6  2.38    N
Perchlorate CDBO-6  1.45    N
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02655    0.537   Y
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02656    100 N
Perchlorate CDV-16-02656    0.394   Y
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    0.174   Y
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02658    4   Y
Perchlorate CDV-16-02658    0.126   Y
Perchlorate CDV-16-02658    0.0561  Y
Perchlorate CDV-16-02658    20  N
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02659    4   N
Nitrate as Nitrogen R-16 S4 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-27    0.003   N
Nitrate as Nitrogen R-31 S2 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen Test Well 1A    0.01    N
Nitrate as Nitrogen Test Well 1A    -0.01   N
Nitrate as Nitrogen Test Well 2 0.04    N
Nitrate as Nitrogen Test Well 2 0.01    N
Nitrate as Nitrogen Test Well 2A    0   N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N

Answer 1

这是一个greatest-n-per-group问题，有许多解决方法(CROSS APPLY、窗口函数、GROUP BY子查询等)。下面是一个使用窗口函数和CTE的方法：

WITH ct AS
  ( SELECT *,
           rn = RANK() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                             ORDER BY Report_Result DESC)
    FROM SLVs_Flagged
  )
SELECT PARAMETER_NAME, GW_LOCATION_ID, 
       Max_Report_Result = Report_Result,
       DETECT_FLAG
       -- more columns
FROM ct
WHERE rn = 1
ORDER BY PARAMETER_NAME, GW_LOCATION_ID ;

查询将返回所有绑定结果(如果有关联的话)。如果您希望每个(PARAMETER_NAME, GW_LOCATION_ID)组合都有一个结果，则可以通过使用ROW_NUMBER()而不是RANK()和修改OVER (..)子句中的ORDER BY来解析领带。例如：(更喜欢DETECT_FLAG和N而不是Y)：

           rn = ROW_NUMBER() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                                   ORDER BY Report_Result DESC, DETECT_FLAG)

Answer 2

我认为以下查询更直观：

create table SLVs_Flagged (PARAMETER_NAME varchar(30), GW_LOCATION_ID varchar(30), Report_Result int, DETECT_FLAG char(1) )
go
insert into dbo.SLVs_Flagged 
values ('abc', 'cor1', 128, 'N'), ('abc', 'cor1', 12, 'Y')
, ('def', 'cor1', 500, 'Y'), ('def', 'cor1', 50, 'N')
go

;with s as (
select max(Report_Result) as Report_Result, PARAMETER_NAME,  GW_LOCATION_ID
from SLVs_Flagged 
group by PARAMETER_NAME, GW_LOCATION_ID)
SELECT s.Report_Result, s.PARAMETER_NAME,  s.GW_LOCATION_ID, t.DETECT_FLAG
FROM SLVs_Flagged t
inner join s 
on t.PARAMETER_NAME = s.PARAMETER_NAME and t.GW_LOCATION_ID = s.GW_LOCATION_ID and t.Report_Result = s.Report_Result