SQL查询最小最大 [英] SQL query minimum maximum

问题描述

我需要获取最大值和最小值，但我还需要在同一行上获取这些最大值或最小值的行ID。

I need to get maximum and minimum values but also I need to get row id of these maximum or minimum on the same row.

SELECT MIN([Value]), MAX([Value]), id 
FROM [AnalystEstimates].[dbo].[AnalystEstimateValues] 
GROUP BY indicatorid

推荐答案

目前尚不清楚您要从问题中得到什么。您真的要使用GROUP BY indicatorid吗？如果不是这样，那很简单，您已经有了很多答案。但是，如果您确实想使用GROUP BY，则难度会更大，而且还没有人完全正确。我还假设您每个指标id只需要一行，并且如果存在重复的行具有相同的最大/最小值，那么最好随意选择其中之一，而不要同时返回两者。

It's very unclear what you want from your question. Do you really want the GROUP BY indicatorid? If not then it's quite simple and you already have many answers. But if you do want to GROUP BY then it's more difficult and no-one has got it quite right yet. I also assume that you only want one row per indicatorid, and if there are duplicate rows that have the same max/min then it's better to just choose one of them arbitrarily instead of returning both.

这是我尝试使用CTE（需要SQL Server 2005或更高版本）：

Here's my attempt, using CTEs (requires SQL Server 2005 or newer):

WITH
    RowNumbers AS (
        SELECT ROW_NUMBER() OVER (ORDER BY indicatorid, value) AS RowNumber, *
        FROM [AnalystEstimates].[dbo].[AnalystEstimateValues]),
    MinRowNumbers AS (
        SELECT indicatorid, MIN(RowNumber) AS RowNumber FROM RowNumbers GROUP BY indicatorid),
    MaxRowNumbers AS (
        SELECT indicatorid, MAX(RowNumber) AS RowNumber FROM RowNumbers GROUP BY indicatorid)
SELECT
    MinRowNumbers.indicatorid,
    RN1.Value AS MinValue,
    RN1.ID AS MinValueId,
    RN2.Value AS MaxValue,
    RN2.ID AS MaxValueId
FROM MinRowNumbers
JOIN MaxRowNumbers ON MinRowNumbers.indicatorid = MaxRowNumbers.indicatorid
JOIN RowNumbers RN1 ON MinRowNumbers.RowNumber = RN1.RowNumber
JOIN RowNumbers RN2 ON MaxRowNumbers.RowNumber = RN2.RowNumber

以下是一些我用来测试的数据：

Here is some data I used to test it:

CREATE TABLE AnalystEstimateValues (ID int, indicatorid int, Value int);

INSERT INTO AnalystEstimateValues (ID, indicatorid , Value) VALUES
(1, 1, 4),
(2, 1, 4),
(3, 2, 6),
(4, 1, 2),
(5, 2, 2),
(6, 2, 5),
(7, 3, 0);

这是我得到的输出：

indicatorid MinValue MinValueId MaxValue MaxValueId
          1        2          4        4          2
          2        2          5        6          3
          3        0          7        0          7

如果这不是您想要的，您能否尝试改善问题以告诉我们您想要什么？

If this isn't what you want, can you please try to improve your question to tell us what you do want?

更新：这是基于Craig Young的答案的替代解决方案，但使用联接而不是子选择：

Update: Here's an alternative solution based on Craig Young's answer but using joins instead of subselects:

WITH
    UniqueIds AS (
        SELECT IndicatorId, Value, MIN(id) AS Id
        FROM AnalystEstimateValues
        GROUP BY IndicatorId, Value)
SELECT
    lims.IndicatorId,
    MinValue,
    T1.Id AS MinValueId,
    MaxValue,
    T2.Id AS MaxValueId 
FROM (
        SELECT
            IndicatorId,
            MIN(Value) as MinValue,
            MAX(Value) as MaxValue
        FROM AnalystEstimateValues
        GROUP BY IndicatorId) lims
JOIN UniqueIds T1 ON lims.IndicatorId = T1.IndicatorId AND lims.MinValue = T1.Value
JOIN UniqueIds T2 ON lims.IndicatorId = T2.IndicatorId AND lims.MaxValue = T2.Value

尽管我还没有运行性能测试来验证这一点，但是它比我的第一个版本更干净，也可能更快。

This is cleaner and probably also faster than my first version, although I haven't run performance tests to verify this.

这篇关于SQL查询最小最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！