查询以获取多个字段的最大/最小行详细信息

Question

我有一个类似于以下示例的表结构：

DateTime              V1    V2    V3    V4    
10/10/10 12:10:00     71    24    33    40
10/10/10 12:00:00     75    22    44    12
10/10/10 12:30:00     44    21    44    33
10/10/10 12:20:00     80    11    88    12

由于 DateTime 字段是唯一字段和关键字段，我想要一个查询来输出每个值的最小和最大日期时间，以便它将显示如下所示的内容：

TYPE    MIN     MINDATETIME         MAX     MAXDATETIME 
V1      44      10/10/10 12:30:00   80      10/10/10 12:20:00 
V2      11      10/10/10 12:20:00   24      10/10/10 12:10:00 
V3      33      10/10/10 12:10:00   88      10/10/10 12:20:00 
V4      12      10/10/10 12:20:00   40      10/10/10 12:10:00 

如果有多行具有相同的最小/最大值，那么它应该获取最新的行。

通过字段上的 Inner Join，我知道要获取字段的最小/最大行的详细信息，但是我认为在一个查询中获取所有内容的唯一方法是将它们全部合并。我认为可能有更好的解决方案。任何帮助表示赞赏。

我正在使用 SQL Server 2008。

谢谢。

Answer 1

• 逆透视到有用的行（“标准化”）
• 计算出每种类型的 MIN/MAX，如果存在关系，则允许 MAX 日期时间
• 提取 MIN/MAX 值

嘿，请先...

DECLARE @foo TABLE (
    DateTimeKey datetime NOT NULL,
    V1 int NOT NULL,
    V2 int NOT NULL,
    V3 int NOT NULL,
    V4 int NOT NULL
);

INSERT @foo (DateTimeKey, V1, V2, V3, V4)
SELECT '10/10/10 12:10:00',     71,    24,    33,    40 
UNION ALL SELECT '10/10/10 12:00:00',     75,    22,    44,    12 
UNION ALL SELECT '10/10/10 12:30:00',     44,    21,    44,    33 
UNION ALL SELECT '10/10/10 12:20:00',     80,    11,    88,    12;


WITH cTE AS 
(
    SELECT
        [Type], [Value], DateTimeKey,
        ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY [Value], DateTimeKey DESC) AS TypeRankMin, 
        ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY [Value] DESC, DateTimeKey DESC) AS TypeRankMax
    FROM
        (
        SELECT
            [Type], [Value], DateTimeKey
        FROM 
           (SELECT DateTimeKey, V1, V2, V3, V4 FROM @foo) p
           UNPIVOT
           ([Value] FOR [Type] IN (V1, V2, V3, V4)) AS unp
        ) bar
)
SELECT
    Mn.[Type], [MIN], MINDATETIME, [MAX], MAXDATETIME
FROM
    (
        SELECT 
            [Type], [Value] AS [MIN], DateTimeKey AS MINDATETIME
        FROM
            cTE
        WHERE
            TypeRankMin = 1
    ) Mn
    JOIN
    (
        SELECT 
            [Type], [Value] AS [MAX], DateTimeKey AS MAXDATETIME
        FROM
            cTE
        WHERE
            TypeRankMax = 1
    ) Mx ON Mn.[Type] = Mx.[Type];

Answer 2

您可以在单个查询中获取最大值和最小值。要获得最大/最小日期时间，您必须将最大/最小查询连接回表（每个值一次），

其效果如下：

select values.*, mint.timestamp, maxt.timestamp from
    (select MIN(value) as minv, MAX(value) as maxv from table group by value) values,
    table mint,
    table maxt
where 
    mint.value = values.minv
    maxt.value = values.maxv

Answer 3

不确定这是否是您所追求的，但它似乎解决了这个基本情况：

create table t(a int,b int);
insert into t(a,b) values(1,2),(2,3),(-1,-10);

select max(t1.a),min(t1.a),max(t2.b),min(t2.b) from t as t1,t as t2;
+-----------+-----------+-----------+-----------+
| max(t1.a) | min(t1.a) | max(t2.b) | min(t2.b) |
+-----------+-----------+-----------+-----------+
|         2 |        -1 |         3 |       -10 |
+-----------+-----------+-----------+-----------+

Answer 4

我将从标准化您的数据开始。由于 V2 不依赖于 V1，因此它们不应位于同一行。这是一个规范化查询：

SELECT DateTime, 'V1' as Type, V1 as Value FROM @data
UNION ALL select DateTime, 'V2', V2 FROM @data
UNION ALL select DateTime, 'V3', V3 FROM @data
UNION ALL select DateTime, 'V4', V4 FROM @data

这样，剩下的就是“选择具有平局的分组最大值的记录”问题。解决该问题的一种方法是：

;WITH normal AS (
    SELECT DateTime, 'V1' as Type, V1 as Value FROM @data
    UNION ALL select DateTime, 'V2', V2 FROM @data
    UNION ALL select DateTime, 'V3', V3 FROM @data
    UNION ALL select DateTime, 'V4', V4 FROM @data
)
SELECT *
FROM    (SELECT  DISTINCT Type FROM normal) dd
CROSS APPLY (
        SELECT   TOP 1 DateTime MINDATETIME, Value MIN
        FROM     normal di
        WHERE    di.Type = dd.Type
        ORDER BY Value, DateTime desc
        ) dimin
CROSS APPLY (
        SELECT   TOP 1 DateTime MAXDATETIME, Value MAX
        FROM     normal di
        WHERE    di.Type = dd.Type
        ORDER BY Value desc, DateTime desc
        ) dimax

dd 查询返回类型（V1、V2，...） 第一个cross apply 搜索该类型的最小值，然后第二个交叉应用搜索最大值。

Answer 5

我读了这个问题并想：我宁愿用 php 而不是 sql 来做到这一点。

然后我读了答案，我想：我真的宁愿用 php 而不是 sql 来做这件事。