在多個欄位中找出最大值

問題

  我個有 Table, 內有5個欄位 A,B,C,D,E，皆為整數。如何判斷 E 的值是最大值

解答

 

use tempdb;
go
create table Test ( A int, B int, C int, D int, E int)
go
insert into Test values ( 1, 2, 3, 4, 5)
go

with CTE (A , B , C , D , E , MaxVal )
as
(
 select A, B, C, D, E, (select max(Val)
                     from   (select A as Val
                             union all
                             select B
                             union all
                             select C
                             union all
                             select D
                             union all
                             select E) as Val) as MaxVal
from Test
)
select A, B, C, D, E, CONVERT(BIT,CASE WHEN E = MaxVal THEN 1
ELSE 0
END) as EIsMax
from CTE