MS SQL 2000 中
表 t1(c1,v1,v2,v3,v4)中有数据
c1,v1,v2,v3,v4
--------------
a, 1, 2, , 1
b, , , 3, 1
--------------
取v1~v4中最大的值显示出来如下:
c1,v
----
a, 2
b, 3
----
注意:
1.其中各列可能有空值
2.尽量写简单...:(
表 t1(c1,v1,v2,v3,v4)中有数据
c1,v1,v2,v3,v4
--------------
a, 1, 2, , 1
b, , , 3, 1
--------------
取v1~v4中最大的值显示出来如下:
c1,v
----
a, 2
b, 3
----
注意:
1.其中各列可能有空值
2.尽量写简单...:(
(select max(v) from (select v1 v union all select v2 union all select v3 union all select v4) b)
from tb
Select c1,v1 from t1
union all
select c1,v2 from t1
union all
select c1,v3 from t1
union all
select c1,v4 from t1) t group by c1
(
select c1,v1 from t
union
select c1,v2 from t
union
select c1,v3 from t
union
select c1,v4 from t
)a
group by c1
returns int
as
begin
declare @i int
set @i=case when @c1 is null then @c2
when @c2 is null then @c1
else case when @c1>@c2 then @c1 else @c2 end
end
return @i
end
go
select c1,dbo.f_compare(dbo.f_compare(dbo.f_compare(v1,v2),v3),v4) from t1
go
(
c1 char(1),
v1 int,
v2 int,
v3 int,
v4 int
)
insert into t1
select 'a',1,2,'',1 union all
select 'b','','',3,1 union all
select 'c',1,2,3,4go
select c1,(select max(v1) v from (select v1 union all
select v2 union all
select v3 union all
select v4) b) from t1
不是偶势利呀,是你写的不符合我的要求呀
--------------
a, 1, 2, , 1
b, , , 3, 1
a, 3,9,,1
要求的结果是a, 2
b, 3
a, 9还是
a,9
b,3如果要求的是下面这种结果,那我没话说.但从你贴子里的要求"取v1~v4中最大的值",没有说c1相同的列对应的v1~v4的最大值