SQL Server一行多列求最大值

最新推荐文章于 2023-06-20 01:27:44 发布

转载最新推荐文章于 2023-06-20 01:27:44 发布 · 1.7k 阅读

文章标签：

#SQL 列最大

SQL 专栏收录该内容

65 篇文章

订阅专栏

本文介绍了在SQL中从多列中找出最大值的几种方法，包括使用子查询、UNPIVOT操作、UNION操作及函数应用等，并提供了具体的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--一行多列求最大值
--创建临时表
create table #temp(id int,createdate1 varchar(17),createdate2 varchar(17),createdate3 varchar(17));
--插入数据
insert into #temp(id,createdate1,createdate2,createdate3)
select 1,'2016-08-16 20:00','2016-08-16 21:00','2016-08-16 22:00'
union all
select 2,'2016-08-15 20:00','2016-08-15 21:00','2016-08-15 22:00'
union all
select 3,'2016-08-14 20:00','2016-08-14 21:00','2016-08-14 22:00';
--求最大值
select id,(select max(createdate) from (values(createdate1),(createdate2),(createdate3)) m(createdate)) createdate
from #temp
--求一行转多列的方法有多种，比如：
--1：构建表值函数将多列以分隔符（比如：逗号）追加到一起，比如：price,num,total
-- 然后通过循环插入到表中，再求最大值
--2：构建标量值函数此方法有局限性，列较少时可以用，建议不用
--3：上面示例
--个人比较看好上面示例

http://blog.youkuaiyun.com/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函数？