select case when a>= b and a>=c and a>=d then a when b>= a and b>=c and b>=d then b when c>= a and c>=b and c>=d then c when d>= a and d>=b and d>=c then d end max_value from tb
declare @t table( name char(10), a float, b float, c float, d float) insert @t select N'张三', 0.11 , 0.21 , 0.29, 0.32 insert @t select N'李四', 0.01 , 0.61 , 0.21, 0.73 select name, case when a>= b and a>=c and a>=d then a when b>= a and b>=c and b>=d then b when c>= a and c>=b and c>=d then c when d>= a and d>=b and d>=c then d end max_value from @t 结果: 张三 0.32 李四 0.73
DECLARE @TB TABLE(a1 INT,a2 INT,a3 INT,a4 INT) INSERT @TB SELECT 5, 6, 7, 9 UNION ALL SELECT 1, 2, 3, 4 UNION ALL SELECT 6, 50, 4, 8SELECT A1=(SELECT MAX(A1) FROM (SELECT A1 UNION SELECT A2 UNION SELECT A3 UNION SELECT A4) A) FROM @TB /* A1 ----------- 9 4 50 */
if object_id('Fun_max')is not null drop Function Fun_max ---創建函數 go create function Fun_max(@a1 int,@a2 int,@a3 int,@a4 int) returns int as begin declare @max int declare @temp table(ma int) insert @temp select @a1 insert @temp select @a2 insert @temp select @a3 insert @temp select @a4 select @max=max(ma) from @temp return @max end go DECLARE @TB TABLE(a1 INT,a2 INT,a3 INT,a4 INT) INSERT @TB SELECT 5, 6, 7, 9 UNION ALL SELECT 1, 2, 3, 4 UNION ALL SELECT 6, 50, 4, 8 select dbo.fun_max(a1,a2,a3,a4)[max] from @tb --調用函數 /*max ----------- 9 4 50 */
看起来好复杂哦,ms 怎么不提供类似 max(a1,a2,a3,a4)的方法呢? 呵呵
select (case when a1>a3 then a1 else a3 end) as amax from( select (case when a1>a2 then a1 else a2 end) as a1,(case when a3>a4 then a3 else a4 end) as a3 from tb )T
DECLARE @T TABLE(A1 INT,A2 INT,A3 INT,A4 INT) INSERT @T SELECT 5,6,7,9 UNION ALL SELECT 1,2,3,4 UNION ALL SELECT 5,60,4,5 SELECT CASE WHEN A1>A2 AND A1>A3 AND A1>A4 THEN A1 WHEN A2>A1 AND A2>A3 AND A2>A4 THEN A2 WHEN A3>A1 AND A3>A2 AND A3>A4 THEN A3 WHEN A4>A1 AND A4>A2 AND A4>A3 THEN A4 END MAXA FROM @T
insert @t select N'张三', 0.11 , 0.21 , 0.29, 0.32 , 0.11, 0.08
insert @t select N'李四', 0.01 , 0.61 , 0.21, 0.73 , 0.21, 0.12
insert @t select N'张五', 0.31 , 0.21 , 0.23, 0.33 , 0.91, 0.65
insert @t select N'张六', 0.59 , 0.11, 0.26, 0.13, 0.01, 0.15 select b.* from
(select x=cast((select * from @t for xml path('r')) as xml)) a
cross apply
(
select name=x.query('./Sname/text()'),v=x.query('max(./*[local-name(.)!="Sname"])') from a.x.nodes('//r') as t(x)
) b/*
张三 0.32
李四 0.73
张五 0.91
张六 0.59
*/
case when a>= b and a>=c and a>=d then
a
when b>= a and b>=c and b>=d then
b
when c>= a and c>=b and c>=d then
c
when d>= a and d>=b and d>=c then
d
end max_value
from tb
declare @t table( name char(10), a float, b float, c float, d float)
insert @t select N'张三', 0.11 , 0.21 , 0.29, 0.32
insert @t select N'李四', 0.01 , 0.61 , 0.21, 0.73 select
name,
case when a>= b and a>=c and a>=d then
a
when b>= a and b>=c and b>=d then
b
when c>= a and c>=b and c>=d then
c
when d>= a and d>=b and d>=c then
d
end max_value
from @t
结果:
张三 0.32
李四 0.73
DECLARE @TB TABLE(a1 INT,a2 INT,a3 INT,a4 INT)
INSERT @TB
SELECT 5, 6, 7, 9 UNION ALL
SELECT 1, 2, 3, 4 UNION ALL
SELECT 6, 50, 4, 8SELECT A1=(SELECT MAX(A1) FROM (SELECT A1 UNION SELECT A2 UNION SELECT A3 UNION SELECT A4) A) FROM @TB
/*
A1
-----------
9
4
50
*/
go
create function Fun_max(@a1 int,@a2 int,@a3 int,@a4 int)
returns int
as
begin
declare @max int
declare @temp table(ma int)
insert @temp select @a1
insert @temp select @a2
insert @temp select @a3
insert @temp select @a4
select @max=max(ma) from @temp
return @max
end
go
DECLARE @TB TABLE(a1 INT,a2 INT,a3 INT,a4 INT)
INSERT @TB
SELECT 5, 6, 7, 9 UNION ALL
SELECT 1, 2, 3, 4 UNION ALL
SELECT 6, 50, 4, 8
select dbo.fun_max(a1,a2,a3,a4)[max] from @tb --調用函數
/*max
-----------
9
4
50
*/
呵呵
select (case when a1>a2 then a1 else a2 end) as a1,(case when a3>a4 then a3 else a4 end) as a3 from tb
)T
INSERT @T SELECT 5,6,7,9
UNION ALL SELECT 1,2,3,4
UNION ALL SELECT 5,60,4,5
SELECT CASE WHEN A1>A2 AND A1>A3 AND A1>A4 THEN A1
WHEN A2>A1 AND A2>A3 AND A2>A4 THEN A2
WHEN A3>A1 AND A3>A2 AND A3>A4 THEN A3
WHEN A4>A1 AND A4>A2 AND A4>A3 THEN A4
END MAXA
FROM @T