select * from f()
结果如下:
id sss qqq
01 1.2 1
02 1 0
03 1.2 0.5
04 1.2 0.5
.. ... ...
希望得到如下结果:
id sss qqq seq
01 1.2 1 1
02 1 0 4
03 1.2 0.5 2
04 1.2 0.5 2
...
先比较SSS的大小,如果相同再比较QQQ的值,排序.
如果都相同,就并列.
空出占用的号码
结果如下:
id sss qqq
01 1.2 1
02 1 0
03 1.2 0.5
04 1.2 0.5
.. ... ...
希望得到如下结果:
id sss qqq seq
01 1.2 1 1
02 1 0 4
03 1.2 0.5 2
04 1.2 0.5 2
...
先比较SSS的大小,如果相同再比较QQQ的值,排序.
如果都相同,就并列.
空出占用的号码
insert @t
select '01',1.2,1 union all
select '02',1,0 union all
select '03',1.2,0.5 union all
select '04',1.2,0.5
select * from @tselect *,(select count(1)+1 from @t where sss+qqq>a.sss+ a.qqq) as seq
from @t a/*id sss qqq
---- ------------------------ ------------------------
01 1.2 1.0
02 1.0 0.0
03 1.2 0.5
04 1.2 0.5(所影响的行数为 4 行)id sss qqq seq
---- ------------------------ ------------------------ -----------
01 1.2 1.0 1
02 1.0 0.0 4
03 1.2 0.5 2
04 1.2 0.5 2*/
declare @t table(id int,sss decimal(5,2),qqq decimal(5,2))
insert @t
select '01', 1.2, 1 union all
select '02', 1, 0 union all
select '03', 1.2, 0.5 union all
select '04', 1.2, 0.5 SELECT *,
seq = (select count(*) + 1 from @t where sss > a.sss or(sss = a.sss and qqq > a.qqq))
FROM @t AS a/*结果
id sss qqq seq
----------- ------- ------- -----------
1 1.20 1.00 1
2 1.00 .00 4
3 1.20 .50 2
4 1.20 .50 2
*/
from f a
insert @t
select '01', 1.2, 1 union all
select '02', 1, 0 union all
select '03', 1.2, 0.5 union all
select '04', 1.2, 0.5 union all
select '05', 0.5, 5.0 /*增加此行*/
SELECT *,
seq = (select count(*) + 1 from @t where sss > a.sss or(sss = a.sss and qqq > a.qqq))
FROM @t AS a
/*结果
id sss qqq seq
----------- ------- ------- -----------
1 1.20 1.00 1
2 1.00 .00 4
3 1.20 .50 2
4 1.20 .50 2
5 .50 5.00 5 /*新增的此行排序正确*/
*/
能否设定一个下限,比如4,如果有并列的seq且seq为最大时,取其下限值.id sss qqq
01 1.2 1
02 1 0
03 1 0
04 1. 0.
希望得到下面的结果:
id sss qqq seq
01 1.2 1 1
02 1 0 4
03 1 0 4
04 1. 0. 4
declare @t table(id int,sss decimal(5,2),qqq decimal(5,2))
insert @t
select '01', 1.2, 1 union all
select '02', 1, 0 union all
select '03', 1, 0 union all
select '04', 1, 0
SELECT *,
seq =
case
when
(select count(*) + 1 from @t where sss > a.sss or(sss = a.sss and qqq > a.qqq))
= (select count(*) + 1 from @t where sss > (select min(sss) from @t))
then
4 /*假设下限为4*/
else
(select count(*) + 1 from @t where sss > a.sss or(sss = a.sss and qqq > a.qqq))
end
FROM @t AS a/*结果
id sss qqq seq
----------- ------- ------- -----------
1 1.20 1.00 1
2 1.00 .00 4
3 1.00 .00 4
4 1.00 .00 4
*/
能否设定一个下限,比如4,如果有仅2个并列的seq且seq为最大时,取其下限值.id sss qqq
01 1.2 1
02 1.2 0
03 1 0
04 1. 0.
希望得到下面的结果:
id sss qqq seq
01 1.2 1 1
02 1.2 0 2
03 1 0 4
04 1. 0. 4
insert @t
select '01', 1.2, 1 union all
select '02', 1.2, 0 union all
select '03', 1, 0 union all
select '04', 1, 0
SELECT *,
seq =
case
when
(select count(*) + 1 from @t where sss > a.sss or(sss = a.sss and qqq > a.qqq))
= (select count(*) + 1 from @t where sss > (select min(sss) from @t))
and (select count(*) from @t where sss = a.sss) = 2 /*有2个并列的seq*/
then
4 /*假设下限为4*/
else
(select count(*) + 1 from @t where sss > a.sss or(sss = a.sss and qqq > a.qqq))
end
FROM @t AS a
/*结果
id sss qqq seq
----------- ------- ------- -----------
1 1.20 1.00 1
2 1.20 .00 2
3 1.00 .00 4
4 1.00 .00 4
*/
SELECT *,
seq = (select count(*) from @t where sss >= a.sss or(sss = a.sss and qqq >= a.qqq))
FROM @t AS a