sql从不同的列获取最大日期 [英] sql get max date from difrent columns

问题描述

您想从多个栏目中检索最长日期

说

i有栏目







a1 | a2 | a3 | a4 | b1 | b2 | b3 | b4



所有这些都是列现在我想要的是我想得到最新的日期

来自a1 | a2 | a3 | a4



和| b1 | b2 | b3 | b4有更多列c1 ......，d1，e1 .........所以只是为了简单起见

i已经提到了两个系列

到目前为止我已写过

hi want to retrieve max date from multiple column
say
i have column



a1|a2|a3|a4|b1|b2|b3|b4

all these are column now what i want is i want to get the latest date
from a1|a2|a3|a4

and |b1|b2|b3|b4 there ar more columns c1......,d1,e1.........so sone just for the sake of simplicity
i have mention two series
so far i have written

Select 
   
  MAX(HepatitisBDate.im_HepatitisBDate1)  as 'iHepatitisBDate1'

   from
   ( 
Select CONVERT(DATE,ISNULL(im_HepatitisBDate1 , CONVERT(DATE,'01/01/1800',103)),103)as im_HepatitisBDate1 from md_Immunization 
Union 
Select CONVERT(DATE,ISNULL(im_HepatitisBDate2 , CONVERT(DATE,'01/01/1800',103)),103)as im_HepatitisBDate1 from md_Immunization 
Union 
Select CONVERT(DATE,ISNULL(im_HepatitisBDate3 , CONVERT(DATE,'01/01/1800',103)),103)as im_HepatitisBDate1 from md_Immunization 
Union 
Select CONVERT(DATE,ISNULL(im_HepatitisBDate4 , CONVERT(DATE,'01/01/1800',103)),103)as im_HepatitisBDate1 from md_Immunization 
) as HepatitisBDate

推荐答案

解决方案1 ​​（使用TOP ， MAX [ ^ ]和 UNPIVOT [ ^ ]）

Solution 1 (using TOP, MAX[^] and UNPIVOT[^])

SELECT TOP(1) FieldName, MAX(DateValue) AS MaxDate
FROM (
    SELECT FieldName, DateValue
    FROM (
        SELECT a1, a2, a3, a4, b1, b2, b3, b4
        FROM TableName
        ) AS pvt
    UNPIVOT (DateValue FOR FieldName IN(a1, a2, a3, a4, b1, b2, b3, b4)) AS unpvt
    ) AS T
GROUP BY FieldName
ORDER BY DateValue DESC

解决方案2 （使用 RANK [ ^ ]和UNPIVOT [ ^ ]）

Solution 2 (using RANK[^] and UNPIVOT[^])

--DROP TABLE #Integers

CREATE TABLE #Integers (a1 INT, a2 INT, a3 INT, a4 INT,
                        b1 INT, b2 INT, b3 INT, b4 INT,
                        c1 INT, c2 INT, c3 INT, c4 INT)

INSERT INTO #Integers (a1, a2, a3, a4,
                        b1, b2, b3, b4,
                        c1, c2, c3, c4)
SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
UNION ALL SELECT 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1
UNION ALL SELECT 8, 5, 2, 7, 4, 1, 9, 6, 3, 1, 2, 3
UNION ALL SELECT 3, 6, 9, 1, 4, 7, 2, 5, 8, 9, 8, 7

SELECT FieldName, FieldValue
FROM (
    SELECT FieldName, FieldValue, RANK() OVER(ORDER BY FieldValue DESC) AS [Rank]
    FROM (
        SELECT *
        FROM #Integers
    ) AS pvt
    UNPIVOT (FieldValue FOR FieldName IN(a1, a2, a3, a4,
                                        b1, b2, b3, b4,
                                        c1, c2, c3, c4)) AS unpvt
) AS T
WHERE [Rank]=1

DROP TABLE #Integers

我认为这可以解决您的问题

http://stackoverflow.com/a/1398019/599560 [ ^ ]

I think this can solve your problem
http://stackoverflow.com/a/1398019/599560[^]

这篇关于sql从不同的列获取最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！