主页 / dba / 问题 / 305795
Accepted
Mr. Geologist
Asked: 2022-01-08 13:47:07 +0800 CST

如何在表中检索最大值及其对应的日期

  • 772

我试图弄清楚如何从数据集中检索最小/最大值和最小/最大值日期，以及与每个最小值/最大值对应的日期值。

示例数据

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 |
sql-server query

2 个回答

  1. 您可以使用公用表表达式 (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;
    • 9
  2. Best Answer

    一种可能更有效但更晦涩的方法是使用您的原始值提取所需的值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约束以确保数据中不存在无意义的温度值。

    • 6

相关问题