主页 / dba / 问题 / 198964
Accepted
Casivio
Asked: 2018-02-28 13:13:48 +0800 CST

在 SQL Server 中按多列分组、聚合其他列并全选

  • 772

我正在使用 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
sql-server partitioning

2 个回答

  1. Best Answer

    这是一个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)
    • 3

  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
    • 0

相关问题