SQL Server一行多列求最大值

張博208發表於2017-02-15
SQLServer
  1. --一行多列求最大值  
  2. --建立臨時表  
  3. create table #temp(id int,createdate1 varchar(17),createdate2 varchar(17),createdate3 varchar(17));  
  4. --插入資料  
  5. insert into #temp(id,createdate1,createdate2,createdate3)  
  6. select 1,'2016-08-16 20:00','2016-08-16 21:00','2016-08-16 22:00'  
  7.  union all  
  8. select 2,'2016-08-15 20:00','2016-08-15 21:00','2016-08-15 22:00'  
  9.  union all  
  10. select 3,'2016-08-14 20:00','2016-08-14 21:00','2016-08-14 22:00';  
  11. --求最大值  
  12. select id,(select max(createdate) from (values(createdate1),(createdate2),(createdate3)) m(createdate)) createdate  
  13.   from #temp  
  14. --求一行轉多列的方法有多種，比如：  
  15. --1：構建表值函式 將多列以分隔符（比如：逗號）追加到一起，比如：price,num,total  
  16. --   然後通過迴圈插入到表中，再求最大值  
  17. --2：構建標量值函式 此方法有侷限性，列較少時可以用，建議不用  
  18. --3：上面示例  
  19. --個人比較看好上面示例 

http://blog.csdn.net/mh942408056/article/details/52225223

Solution 1

The first solution is the following:

SELECT 
   ID, 
   (SELECT MAX(LastUpdateDate)
      FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate)) 
   AS LastUpdateDate
FROM ##TestTable

Solution 2

We can accomplish this task by using UNPIVOT:

SELECT ID, MAX(UpdateDate) AS LastUpdateDate 
FROM ##TestTable
UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u
GROUP BY ID, Name

Solution 3

This task can be solved by using UNION:

SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM
(
	SELECT ID, UpdateByApp1Date AS UpdateDate
	FROM ##TestTable
	UNION 
	SELECT ID, UpdateByApp2Date AS UpdateDate
	FROM ##TestTable
	UNION 
	SELECT ID, UpdateByApp3Date AS UpdateDate
	FROM ##TestTable
) ud
GROUP BY ID

Solution 4

And the fourth solution also uses a UNION:

SELECT  ID,
( SELECT MAX(UpdateDate) AS LastUpdateDate
  FROM      
  ( SELECT tt.UpdateByApp1Date AS UpdateDate
    UNION
    SELECT tt.UpdateByApp2Date
    UNION
    SELECT tt.UpdateByApp3Date
   ) ud
 ) LastUpdateDate 
FROM ##TestTable tt
https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/

SELECT MostRecentDate 
FROM SourceTable
    CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) m
https://stackoverflow.com/questions/71022/sql-max-of-multiple-columns

If you're using MySQL, you can use

SELECT GREATEST(col1, col2 ...) FROM table
注意此函式  LEAST函式？


相關文章