在 SQL Server 中按多列分組、聚合其他列並全選

我正在使用 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’ 在選擇列表中無效，因為它不包含在聚合函式或 GROUP BY 子句中。 ”我想要的是值MAX( Report_Result ) 返回的行的 DETECT_FLAG。

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 ) 的****DETECT_FLAG值，但我嘗試使用 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

這是一個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()並修改子句ORDER BY內部來解決關係。OVER (..)例如。（更喜歡DETECT_FLAGover N）Y：

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

引用自：https://dba.stackexchange.com/questions/198964