select ID
,Name
,(case when Score1>=Score2 and Score1>=Score3
then Score1
when Score2>=Score3 and Score2>=Score1
then Score2
else Score3
end
) as 'MaxScore'
from 表
,Name
,(case when Score1>=Score2 and Score1>=Score3
then Score1
when Score2>=Score3 and Score2>=Score1
then Score2
else Score3
end
) as 'MaxScore'
from 表
insert into @A
SELECT 1,'a', 0,80, 0 UNION
SELECT 2,'b', 90,66,0 UNION
SELECT 3,'c', 0, 0 ,88 UNION
SELECT 4,'d', 22,0,33
SELECT ID,NAME,MAXSCORE=(CASE WHEN SCORE1>=SCORE2 AND SCORE2>=SCORE3 THEN SCORE1
when Score2>=Score3 and Score2>=Score1 THEN SCORE2
ELSE Score3 END)
FROM @A
--结果:--ID Name MaxScore
--1 a 80
--2 b 90
--3 c 88
--4 d 33
这样得定义还真没有见过,
我觉得是否把它导出到excel中,用求最大值得函数(max)来得更方便?
嘿嘿~~如果是我自己建表当然不是这个样子,这个是我帮朋友统计些数据的,表是他们的呵。
它的方式比較行了
--建立測試環境
create table Score(ID int,Name varchar(10),Score1 int,Score2 int ,Score3 int)
insert into Score
select 1,'a', 0,80, 0 union all
select 2,'b', 90,66,0 union all
select 3,'c', 0, 0 ,88 union all
select 4,'d', 22,0,33
--測試
select ID,Name,dbo.GetMax(JoinSocre,';') MaxScore
from
(
select ID,Name,cast(Score1 as varchar)+';'+cast(Score2 as varchar)+';'+cast(Score3 as varchar) JoinSocre
from Score
)A
--刪除測試環境
drop table Score
/*
顯示結果
ID Name MaxScore
1 a 80
2 b 90
3 c 88
4 d 33
*/
create function GetMax(@vString varchar(100),@cDivisionChar varchar(10))
returns int
as
begin
declare @iCount int,@iPos int,@vGetString int,@iMax int --@vString varchar(100),
if(right(rtrim(@vString),1)<>@cDivisionChar)
begin
set @vString=@vString+@cDivisionChar
end
select @iCount=len(@vString)-len(replace(@vString,@cDivisionChar,'')),@iPos=1,@vGetString='',@iMax=0
while(@iCount>=@iPos)
begin
set @vGetString = cast(left(@vString,charindex(@cDivisionChar,@vString)-1) as int)
if(@iMax<@vGetString)
begin
set @iMax=@vGetString
end
set @vString=right(@vString,len(@vString)-charindex(@cDivisionChar,@vString))
set @iPos=@iPos+1
end
return @iMax
end
--
想實現動態的話就用動態語句結合系統syscolumns
例如:
select * from syscolumns where id=object_id('Score')
樓主可以去試試