[sql] 여러 열의 SQL MAX?

Program / 글쓴이

여러 열의 최대 행당 1 값을 어떻게 반환합니까?

TableName

[Number, Date1, Date2, Date3, Cost]

다음과 같은 것을 반환해야합니다.

[Number, Most_Recent_Date, Cost]

질문?

답변

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의 더 간단한 답변을 고려할 수 있습니다.]

답변

다음은 MaxT-SQL 및 SQL Server를 사용 하는 기능에 대한 또 다른 훌륭한 솔루션입니다.

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

답변

MySQL을 사용하는 경우 사용할 수 있습니다

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

답변

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 (Simulated 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

답변

아래 두 샘플 중 하나가 작동합니다.

SELECT  MAX(date_columns) AS max_date
FROM    ( (SELECT   date1 AS date_columns
           FROM     data_table         )
          UNION
          ( SELECT  date2 AS date_columns
            FROM    data_table
          )
          UNION
          ( SELECT  date3 AS date_columns
            FROM    data_table
          )
        ) AS date_query

두 번째는 애드온입니다 lassevk의 답변 .

SELECT  MAX(MostRecentDate)
FROM    ( 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
          FROM      data_table
        ) AS date_query

답변

T-SQL의 경우 (MSSQL 2008+)

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

답변

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