一千萬個為什麽

搜索

SQL查詢最小值最大值

我需要獲得最大值和最小值,但我還需要在同一行上獲得這些最大值或最小值的行ID。

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

最佳答案

目前還不清楚你的問題是什麽。你真的想要GROUP BY indicatorid嗎?如果沒有,那麽它很簡單,你已經有很多答案。但是,如果你確實想要GROUP BY那麽它就更難了,而且沒有人能夠把它做得很好。我還假設你只需要每個indicatorid一行,並且如果有重復的行具有相同的最大/最小值,那麽最好是任意選擇其中一個,而不是返回兩個行。

這是我嘗試使用CTE(需要SQL Server 2005或更新版本):

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

以下是我用來測試它的一些數據:

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

如果這不是您想要的,請您嘗試改進您的問題,告訴我們您的需求嗎?


更新:這是基於Craig Young答案的替代解決方案,但使用連接而不是子選擇:

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

雖然我沒有運行性能測試來驗證這一點,但它比我的第一個版本更清晰,也可能更快。

轉載註明原文: SQL查詢最小值最大值