多列的SQL MAX?




sql max group by (14)

不幸的是, 拉塞斯的答案似乎很明显,但有一个重要的缺陷。 它不能处理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

你如何返回每列最多几列的1个值:

表名

[Number, Date1, Date2, Date3, Cost]

我需要返回这样的内容:

[Number, Most_Recent_Date, Cost]

查询?


从SQL Server 2012开始,我们可以使用IIF

 DECLARE @Date1 DATE='2014-07-03';
 DECLARE @Date2 DATE='2014-07-04';
 DECLARE @Date3 DATE='2014-07-05';

 SELECT IIF(@Date1>@Date2,
        IIF(@Date1>@Date3,@Date1,@Date3),
        IIF(@Date2>@Date3,@Date2,@Date3)) AS MostRecentDate

你可以在你传递日期的地方创建一个函数,然后将函数添加到下面的select语句中。 选择Number,dbo.fxMost_Recent_Date(Date1,Date2,Date3),Cost

create FUNCTION  fxMost_Recent_Date 

(@ Date1 smalldatetime,@ Date2 smalldatetime,@ Date3 smalldatetime)RETURNS smalldatetime AS BEGIN DECLARE @Result smalldatetime

declare @MostRecent smalldatetime

set @MostRecent='1/1/1900'

if @Date1>@MostRecent begin set @[email protected] end
if @Date2>@MostRecent begin set @[email protected] end
if @Date3>@MostRecent begin set @[email protected] end
RETURN @MostRecent

结束


使用CROSS APPLY(for 2005+)....

SELECT MostRecentDate 
FROM SourceTable
    CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md

另外有3种方法,其中UNPIVOT (1)是迄今为止最快的,其次是模拟不透明(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


如果您正在使用SQL Server 2005,则可以使用UNPIVOT功能。 这是一个完整的例子:

create table dates 
(
  number int,
  date1 datetime,
  date2 datetime,
  date3 datetime 
)

insert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008')
insert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008')
insert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008')
insert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008')

select max(dateMaxes)
from (
  select 
    (select max(date1) from dates) date1max, 
    (select max(date2) from dates) date2max,
    (select max(date3) from dates) date3max
) myTable
unpivot (dateMaxes For fieldName In (date1max, date2max, date3max)) as tblPivot

drop table dates

对于T-SQL(MSSQL 2008+)

SELECT
  (SELECT
     MAX(MyMaxName) 
   FROM ( VALUES 
            (MAX(iSortCode)), 
            (MAX(Field2)) 
        ) MyAlias(MyMaxName)
  ) 
FROM MyTable1

标量函数会导致各种性能问题,因此如果可能的话,最好将逻辑封装到内联表值函数中。 这是我用来替换一些用户定义函数的函数,它从最多10个日期列表中选择最小/最大日期。 当在我的数据集上测试了100万行时,标量函数在我杀死查询之前花了15分钟,Inline TVF花费了1分钟,这与选择结果集到临时表中的时间相同。 使用这个函数可以从SELECT或者CROSS APPLY中的子查询中调用该函数。

CREATE FUNCTION dbo.Get_Min_Max_Date
(
    @Date1  datetime,
    @Date2  datetime,
    @Date3  datetime,
    @Date4  datetime,
    @Date5  datetime,
    @Date6  datetime,
    @Date7  datetime,
    @Date8  datetime,
    @Date9  datetime,
    @Date10 datetime
)
RETURNS TABLE
AS
RETURN
(
    SELECT      Max(DateValue)  Max_Date,
                Min(DateValue)  Min_Date
    FROM        (
                    VALUES  (@Date1),
                            (@Date2),
                            (@Date3),
                            (@Date4),
                            (@Date5),
                            (@Date6),
                            (@Date7),
                            (@Date8),
                            (@Date9),
                            (@Date10)
                )   AS Dates(DateValue)
)

请尝试使用UNPIVOT

SELECT MAX(MaxDt) MaxDt
   FROM tbl 
UNPIVOT
   (MaxDt FOR E IN 
      (Date1, Date2, Date3)
)AS unpvt;

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

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

那么,你可以使用CASE语句:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

[对于Microsoft SQL Server 2008及更高版本,您可以考虑下面的Sven更简单的答案。]


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

SELECT 
    CASE 
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1 
        WHEN Date2 >= Date3 THEN Date2 
        ELSE Date3
    END AS MostRecentDate 

随着案例陈述按顺序进行评估,写出并跳过评估步骤稍微容易一些。





tsql