如何在表中檢索最大值及其對應的日期

我試圖弄清楚如何從數據集中檢索最小/最大值和最小/最大值日期，以及與每個最小值/最大值對應的日期值。

範例數據

CREATE TABLE mytable
   ([ID] int, [TEMP] FLOAT, [DATE] DATE)
;

INSERT INTO mytable
   ([ID], [TEMP], [DATE])
VALUES
   (8305,  16.38320208,  '03/22/2002'),
   (8305,  17.78320208,  '11/15/2010'),
   (8305,  16.06320208,  '03/11/2002'),
   (8305,  18.06320208,  '02/01/2007'),
   (2034,  5.2,  '03/12/1985'),
   (2034,  2.24,  '05/31/1991'),
   (2034,  6.91,  '09/15/1981'),
   (2034,  7.98,  '07/16/1980'),
   (2034,  10.03,  '03/21/1979'),
   (2034,  6.85,  '11/19/1982')
;

查詢TEMP和DATE列的最小值/最大值：

SELECT ID,
   COUNT(TEMP) AS COUNT,
   MAX(TEMP) AS MAXTEMP,
   MAX(DATE) AS MAXDATE
FROM mytable
GROUP BY ID;

檢索這個：

|   ID | COUNT |     MAXTEMP |    MAXDATE |
|------|-------|-------------|------------|
| 2034 |     6 |       10.03 | 1991-05-31 |
| 8305 |     4 | 18.06320208 | 2010-11-15 |

但我想弄清楚如何檢索這個：

|   ID | COUNT |     MAXTEMP |MAXTEMPDATE |    MAXDATE|
| 2034 |     6 |       10.03 | 1979-03-21 |1991-05-31 |
| 8305 |     4 | 18.06320208 | 2007-02-01 |2010-11-15 |

您可以使用公用表表達式 (CTE)和ROW_NUMBER()來實現它：

;WITH cte AS
(
  SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [TEMP] DESC) AS rn
  FROM mytable
)
SELECT cte.ID, 
   COUNT(*) AS COUNT, 
   cte.TEMP AS MAXTEMP, 
   cte.DATE AS MAXTEMPDATE,
   MAX(mt.DATE) AS MAXDATE
FROM cte
   INNER JOIN mytable mt ON cte.ID = mt.ID
WHERE rn = 1
GROUP BY cte.ID, 
   cte.TEMP, 
   cte.DATE;

一種可能更有效但更晦澀的方法是使用您的原始值提取所需的值group by

SELECT ID,
      COUNT = COUNT(TEMP),
      MAXTEMP = MAX(TEMP),
      MAXTEMPDATE = CAST(RIGHT(MAX(FORMAT(CAST([TEMP] + 500 AS DECIMAL(15, 10)), '00000.0000000000')
                                   + FORMAT([DATE], 'yyyy-MM-dd')), 10) AS DATE),
      MAXDATE = MAX(DATE)
FROM   mytable
GROUP  BY ID; 

因為溫度可能是負數，所以首先將它們全部增加 500，所以我不必擔心負數（絕對零是 -273.15 °C/-459.67 °F）。調整後的值永遠不會被使用，除非被使用，MAX所以不需要在任何地方反轉它。

然後，它將結果轉換為 16 個字元的字元串，其中前 5 個字元保留給整數部分，最後 10 個字元保留給小數部分（必要時兩邊都用零填充）。這意味著值的字典排序將匹配數字排序。

最後將 Date 附加到它的末尾 -MAX找到並從連接字元串中的已知位置檢索 Date。

如果你有一個關於mytable(ID) INCLUDE (TEMP, DATE)執行計劃的索引，這是非常有效的。

---

至於為什麼負值會在這裡出現潛在問題，這是因為我們必須確保字元串表示的排序方式與數字表示的排序方式完全相同。我的第一個想法是有一個表示符號的前導字元，因此首先對負值進行排序。下一個問題是-12高於-20但字元串表示00020會排序高於00012。對溫度保持的值域應用合理的偏移量可以讓我避免所有這些。還應用CHECK約束以確保數據中不存在無意義的溫度值。

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