select sname, vmax=max(value)
from (
select sname, value=v1 from table union
select sname, value=v2 from table union
select sname, value=v3 from table union
select sname, value=v4 from table union
select sname, value=v5 from table union
select sname, value=v6 from table
) as a
group by sname
from (
select sname, value=v1 from table union
select sname, value=v2 from table union
select sname, value=v3 from table union
select sname, value=v4 from table union
select sname, value=v5 from table union
select sname, value=v6 from table
) as a
group by sname
GO
--建立自定义函数IIF
CREATE FUNCTION IIF
(
@NUM1 FLOAT,
@NUM2 FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @MAXNUM FLOAT
SET @MAXNUM=(CASE WHEN @NUM1 IS NULL AND @NUM2 IS NULL THEN NULL
WHEN @NUM1 IS NULL AND @NUM2 IS NOT NULL THEN @NUM2
WHEN @NUM1 IS NOT NULL AND @NUM2 IS NULL THEN @NUM1
WHEN @NUM1 IS NOT NULL AND @NUM2 IS NOT NULL THEN (CASE WHEN @NUM1>@NUM2 THEN @NUM1 ELSE @NUM2 END )
END)
RETURN @MAXNUM
END
GO--建立测试数据
CREATE TABLE TB(Sname NVARCHAR(10),V1 FLOAT,V2 FLOAT,V3 FLOAT,V4 FLOAT,V5 FLOAT,V6 FLOAT)
INSERT INTO TB
SELECT N'张三',0.11,0.21,0.29,0.32,0.11,0.08 UNION ALL
SELECT N'李四',0.01,0.61,0.21,0.73,0.21,0.12 UNION ALL
SELECT N'张五',0.31,0.21,0.23,0.33,0.91,0.65 UNION ALL
SELECT N'张六',0.59,0.11,0.26,0.13,0.01,0.15 --查询数据
SELECT Sname,VMAX=[Test].[dbo].[IIF] ([Test].[dbo].[IIF] ([Test].[dbo].[IIF] ([Test].[dbo].[IIF] ([Test].[dbo].[IIF] (V1,V2),V3),V4),V5),V6)
FROM TB--删除测试数据
DROP TABLE TB--测试结果:
/*(4 個資料列受到影響)
Sname VMAX
---------- ----------------------
张三 0.32
李四 0.73
张五 0.91
张六 0.59(4 個資料列受到影響)*/