求多列最大值的问题

Question

比如表结构
id  a b c d
001 2 5 7 9
002 4 1 5 7
003 9 2 4 0
...我想知道每行最大的数值,以及这个数值对应的列名. 因为我需要根据列名做一些进一步的处理
只知道数值好弄,但怎么把列名带上呢,比如对于第一行,我需要的信息包括
001 9 d

Answer 1

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id varchar(3),a int,b int,c int,d int)
insert into #tb
select '001',2,5,7,9 union all
select '002',4,1,5,7 union all
select '003',9,2,4,0;with cte as(
select id,num=a,field='a' from #tb
union all 
select id,num=b,field='b' from #tb
union all 
select id,num=c,field='c' from #tb
union all 
select id,num=d,field='d' from #tb
)
select * from cte t
where not exists(select * from cte where id=t.id and num>t.num)
order by idid   num         field
---- ----------- -----
001  9           d
002  7           d
003  9           a(3 行受影响)

Answer 2

create table #tb (id varchar(3),a int,b int,c int,d int)
insert into #tb
select '001',2,5,7,9 union all
select '002',4,1,5,7 union all
select '003',9,2,4,0SELECT id,ch,VALUE
FROM(
SELECT id,ch,value,max(VALUE)OVER(PARTITION BY id )maxValue
FROM(
SELECT id,CH,VALUE
FROM #Tb
UNPIVOT(VALUE FOR CH IN([a],[b],[c],[d]))UNPVT
)M
)P
WHERE VALUE=maxValueid   ch           VALUE
---- ----------------------
001  d              9
002  d              7
003  a              9(3 row(s) affected)

Answer 3

这样也可以
SELECT * FROM 
(
SELECT id,CH,VALUE
FROM #Tb
UNPIVOT(VALUE FOR CH IN([a],[b],[c],[d]))UNPVT
)M  
WHERE NOT EXISTS (SELECT NULL FROM 
( SELECT id,CH,VALUE
FROM #Tb
UNPIVOT(VALUE FOR CH IN([a],[b],[c],[d]))UNPVT
)p 
WHERE p.id=m.id AND m.value<p.VALUE)id   ch           VALUE
---- ----------------------
001  d              9
002  d              7
003  a              9(3 row(s) affected）

Answer 4

参考精华帖 MSSQL多列取最大或者最小值 --整理帖