--这样不动脑子的写法也凑合了declare @test table ( id char(2), a int, b int, c int ) insert into @test select '01',4,1,5 union all select '02',1,1,3 union all select '03',5,5,3 union all select '04',2,2,2select * from @testselect id, case when a<=b and a<=c then 1 when a<=b and a>c then 2 when a>b and a<=c then 2 when a>b and a>c then 3 end as a, case when b<=a and b<=c then 1 when b<=a and b>c then 2 when b>a and b<=c then 2 when b>a and b>c then 3 end as b, case when c<=a and b>=c then 1 when c<=a and b<c then 2 when c>a and b>=c then 2 when c>a and b<c then 3 end as c from @test a/* id a b c ---- ----------- ----------- ----------- 01 4 1 5 02 1 1 3 03 5 5 3 04 2 2 2id a b c ---- ----------- ----------- ----------- 01 2 1 3 02 1 1 3 03 2 2 1 04 1 1 1 */
declare @test table ( id char(2), a int, b int, c int ) insert into @test select '01',4,1,5 union all select '02',1,1,3 union all select '03',5,5,3 union all select '04',2,2,2select id, ( select count(1)+1 from ( select a union all select b union all select c ) x where x.a<y.a ) a, ( select count(1)+1 from ( select a union all select b union all select c ) x where x.a<y.b ) b, ( select count(1)+1 from ( select a union all select b union all select c ) x where x.a<y.c ) cfrom @test y??
(
id char(2),
a int,
b int,
c int
)
insert into @test
select '01',4,1,5 union all
select '02',1,1,3 union all
select '03',5,5,3 union all
select '04',2,2,2select * from @testselect
id,
case
when a<=b and a<=c then 1
when a<=b and a>c then 2
when a>b and a<=c then 2
when a>b and a>c then 3 end as a,
case
when b<=a and b<=c then 1
when b<=a and b>c then 2
when b>a and b<=c then 2
when b>a and b>c then 3 end as b,
case
when c<=a and b>=c then 1
when c<=a and b<c then 2
when c>a and b>=c then 2
when c>a and b<c then 3 end as c
from @test a/*
id a b c
---- ----------- ----------- -----------
01 4 1 5
02 1 1 3
03 5 5 3
04 2 2 2id a b c
---- ----------- ----------- -----------
01 2 1 3
02 1 1 3
03 2 2 1
04 1 1 1
*/
(
id char(2),
a int,
b int,
c int
)
insert into @test
select '01',4,1,5 union all
select '02',1,1,3 union all
select '03',5,5,3 union all
select '04',2,2,2select id,
(
select count(1)+1 from
(
select a
union all select b
union all select c
) x
where x.a<y.a
) a,
(
select count(1)+1 from
(
select a
union all select b
union all select c
) x
where x.a<y.b
) b,
(
select count(1)+1 from
(
select a
union all select b
union all select c
) x
where x.a<y.c
) cfrom @test y??