如何在几列的最大值中每行返回1个值:

的表

[Number, Date1, Date2, Date3, Cost]

我需要返回这样的东西:

[Number, Most_Recent_Date, Cost]

查询?


当前回答

DECLARE @TableName TABLE (Number INT, Date1 DATETIME, Date2 DATETIME, Date3 DATETIME, Cost MONEY)

INSERT INTO @TableName 
SELECT 1, '20000101', '20010101','20020101',100 UNION ALL
SELECT 2, '20000101', '19900101','19980101',99 

SELECT Number,
       Cost  ,
       (SELECT MAX([Date])
       FROM    (SELECT Date1 AS [Date]
               UNION ALL
               SELECT Date2
               UNION ALL
               SELECT Date3
               )
               D
       )
       [Most Recent Date]
FROM   @TableName

其他回答

还有3种方法,其中UNPIVOT(1)是目前为止最快的,其次是模拟UNPIVOT(3),它比(1)慢得多,但仍然比(2)快得多

CREATE TABLE dates
    (
      number INT PRIMARY KEY ,
      date1 DATETIME ,
      date2 DATETIME ,
      date3 DATETIME ,
      cost INT
    )

INSERT  INTO dates
VALUES  ( 1, '1/1/2008', '2/4/2008', '3/1/2008', 10 )
INSERT  INTO dates
VALUES  ( 2, '1/2/2008', '2/3/2008', '3/3/2008', 20 )
INSERT  INTO dates
VALUES  ( 3, '1/3/2008', '2/2/2008', '3/2/2008', 30 )
INSERT  INTO dates
VALUES  ( 4, '1/4/2008', '2/1/2008', '3/4/2008', 40 )
GO

解决方案1 (UNPIVOT)

SELECT  number ,
        MAX(dDate) maxDate ,
        cost
FROM    dates UNPIVOT ( dDate FOR nDate IN ( Date1, Date2,
                                            Date3 ) ) as u
GROUP BY number ,
        cost 
GO

解决方案2(每行子查询)

SELECT  number ,
        ( SELECT    MAX(dDate) maxDate
          FROM      ( SELECT    d.date1 AS dDate
                      UNION
                      SELECT    d.date2
                      UNION
                      SELECT    d.date3
                    ) a
        ) MaxDate ,
        Cost
FROM    dates d
GO

方案3(模拟UNPIVOT)

;WITH    maxD
          AS ( SELECT   number ,
                        MAX(CASE rn
                              WHEN 1 THEN Date1
                              WHEN 2 THEN date2
                              ELSE date3
                            END) AS maxDate
               FROM     dates a
                        CROSS JOIN ( SELECT 1 AS rn
                                     UNION
                                     SELECT 2
                                     UNION
                                     SELECT 3
                                   ) b
               GROUP BY Number
             )
    SELECT  dates.number ,
            maxD.maxDate ,
            dates.cost
    FROM    dates
            INNER JOIN MaxD ON dates.number = maxD.number
GO

DROP TABLE dates
GO

如果你正在使用MySQL或PostgreSQL或Oracle或BigQuery,你可以使用

SELECT GREATEST(col1, col2 ...) FROM table

不幸的是,拉斯的答案虽然看似显而易见,但却有一个关键的缺陷。它不能处理NULL值。任何一个NULL值都会返回Date1。不幸的是,任何试图解决这个问题的尝试往往会变得非常混乱,并且不能很好地扩展到4或更多的值。

Databyss的第一个答案看起来(现在也是)不错。但是,不清楚答案是否可以轻易地从多表连接中推断出3个值,而不是从单个表中推断出3个值。我想避免将这样的查询变成子查询,只是为了得到最多3列,而且我非常确定databyss的优秀想法可以被清理一点。

废话不多说,下面是我的解决方案(源自databyss的想法)。 它使用交叉连接选择常量来模拟多表连接的效果。需要注意的重要一点是,所有必要的别名都可以正确地执行(并非总是如此),这使得模式非常简单,并且可以通过额外的列进行相当的可伸缩性。

DECLARE @v1 INT ,
        @v2 INT ,
        @v3 INT
--SET @v1 = 1 --Comment out SET statements to experiment with 
              --various combinations of NULL values
SET @v2 = 2
SET @v3 = 3

SELECT  ( SELECT    MAX(Vals)
          FROM      ( SELECT    v1 AS Vals
                      UNION
                      SELECT    v2
                      UNION
                      SELECT    v3
                    ) tmp
          WHERE     Vals IS NOT NULL -- This eliminates NULL warning

        ) AS MaxVal
FROM    ( SELECT    @v1 AS v1
        ) t1
        CROSS JOIN ( SELECT @v2 AS v2
                   ) t2
        CROSS JOIN ( SELECT @v3 AS v3
                   ) t3

最后,针对以下内容:

SQL Server 2022 (16.x)预览 Azure SQL数据库 Azure SQL托管实例

我们也可以用GREATEST。与其他T-SQL函数类似,这里有一些重要的注意事项:

如果所有参数都具有相同的数据类型,并且 的类型为 支持 进行比较, GREATEST将返回该类型; 否则, 函数 将隐式地将所有参数转换为 最高优先级 的数据类型进行比较,并使用 这个 类型 作为返回类型; 如果一个或多个参数不是NULL,那么NULL参数将在比较过程中被忽略;如果所有参数都为NULL,则GREATEST将返回NULL;

在GREATEST中不支持以下类型进行比较:varchar(max)、varbinary(max)或nvarchar(max)超过8,000字节、游标、几何、地理、图像、非字节顺序的用户定义类型、ntext、table、text和xml。

下面是使用T-SQL和SQL Server实现Max功能的另一个很好的解决方案

SELECT [Other Fields],
  (SELECT Max(v) 
   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]

Values是表值构造函数。

"指定要构造到表中的一组行值表达式。Transact-SQL表值构造函数允许在单个DML语句中指定多行数据。表值构造函数可以指定为INSERT…VALUES语句,或者作为MERGE语句的USING子句或FROM子句中的派生表。