--> Test Data: @T declare @T table ([F1] varchar(2),[F2] varchar(2),[F3] varchar(2)) insert into @T select 'a1','a2','a3' union all select 'b1','b2','b3' union all select 'a1','c2','c3' union all select 'b1','d2','d3' union all select 'b1','e2','e3' union all select 'a1','f2','f3' union all select 'a1','g2','g3'select * from @T --Code select id=(select COUNT(*)+1 from @T where F1=a.F1 and F2<a.F2), * from @T a order by F1,F2--Result /* id F1 F2 F3 ----------- ---- ---- ---- 1 a1 a2 a3 2 a1 c2 c3 3 a1 f2 f3 4 a1 g2 g3 1 b1 b2 b3 2 b1 d2 d3 3 b1 e2 e3 */
declare @t table ( F1 char(2), F2 char(2), F3 char(2) )insert @t select 'a1','a2','a3' union all select 'b1','b2','b3' union all select 'a1','c2','c3' union all select 'b1','d2','d3' union all select 'b1','e2','e3' union all select 'a1','f2','f3' union all select 'a1','g2','g3'select id = ( select count(*) from @t where F1 = a.F1 and F2 <= a.F2 ), a.F1, a.F2, a.F3 from @t a order by a.F1,id/** 1 a1 a2 a3 2 a1 c2 c3 3 a1 f2 f3 4 a1 g2 g3 1 b1 b2 b3 2 b1 d2 d3 3 b1 e2 e3 **/
保险点Ldeclare @t table ( F1 char(2), F2 char(2), F3 char(2) )insert @t select 'a1','a2','a3' union all select 'b1','b2','b3' union all select 'a1','c2','c3' union all select 'b1','d2','d3' union all select 'b1','e2','e3' union all select 'a1','f2','f3' union all select 'a1','g2','g3'select id = ( select count(*) from @t where F1 = a.F1 and checksum(F2 ,f3)<= checksum(a.F2,a.f3) ), a.F1, a.F2, a.F3 from @t a order by a.F1,id /* id F1 F2 F3 ----------- ---- ---- ---- 1 a1 a2 a3 2 a1 c2 c3 3 a1 f2 f3 4 a1 g2 g3 1 b1 b2 b3 2 b1 d2 d3 3 b1 e2 e3(7 行受影响) */
谢谢,各位的指教,这样只适合f1字段数据相同的情况下,f2字段不相同,如果f2也相同呢?
--2005 SELECT ROW_NUMBER() OVER(ORDER BY f2 DESC) AS ID, f1, f2, f3 FROM tb order by id asc
declare @T table ([F1] varchar(2),[F2] varchar(2),[F3] varchar(2)) insert into @T select 'a1','a2','a3' union all select 'b1','b2','b3' union all select 'a1','c2','c3' union all select 'b1','d2','d3' union all select 'b1','e2','e3' union all select 'a1','f2','f3' union all select 'a1','g2','g3' SELECT DENSE_RANK() OVER (PARTITION BY f1 order by f2) id,f1,f2,f3 FROM @t /* 1 a1 a2 a3 2 a1 c2 c3 3 a1 f2 f3 4 a1 g2 g3 1 b1 b2 b3 2 b1 d2 d3 3 b1 e2 e3 */
declare @T table ([F1] varchar(2),[F2] varchar(2),[F3] varchar(2))
insert into @T
select 'a1','a2','a3' union all
select 'b1','b2','b3' union all
select 'a1','c2','c3' union all
select 'b1','d2','d3' union all
select 'b1','e2','e3' union all
select 'a1','f2','f3' union all
select 'a1','g2','g3'select * from @T
--Code
select
id=(select COUNT(*)+1 from @T where F1=a.F1 and F2<a.F2),
* from @T a order by F1,F2--Result
/*
id F1 F2 F3
----------- ---- ---- ----
1 a1 a2 a3
2 a1 c2 c3
3 a1 f2 f3
4 a1 g2 g3
1 b1 b2 b3
2 b1 d2 d3
3 b1 e2 e3
*/
declare @t table
(
F1 char(2),
F2 char(2),
F3 char(2)
)insert @t select 'a1','a2','a3'
union all select 'b1','b2','b3'
union all select 'a1','c2','c3'
union all select 'b1','d2','d3'
union all select 'b1','e2','e3'
union all select 'a1','f2','f3'
union all select 'a1','g2','g3'select
id = (
select count(*)
from @t
where F1 = a.F1
and F2 <= a.F2
),
a.F1,
a.F2,
a.F3
from @t a
order by a.F1,id/**
1 a1 a2 a3
2 a1 c2 c3
3 a1 f2 f3
4 a1 g2 g3
1 b1 b2 b3
2 b1 d2 d3
3 b1 e2 e3
**/
---------------
insert @t select 'a1', 'a2' , 'a3'
insert @t select 'b1', 'b2' , 'b3'
insert @t select 'a1', 'c2' , 'c3'
insert @t select 'b1', 'd2' , 'd3'
insert @t select 'b1', 'e2' , 'e3'
insert @t select 'a1', 'f2' , 'f3'
insert @t select 'a1', 'g2' , 'g3'
select id=(select count(*)+1 from @t where f1=t.f1 and f2<t.f2),f2,f3 from @t t order by f1,f2
/*
id f2 f3
----------- ---------- ----------
1 a2 a3
2 c2 c3
3 f2 f3
4 g2 g3
1 b2 b3
2 d2 d3
3 e2 e3*/
(
F1 char(2),
F2 char(2),
F3 char(2)
)insert @t select 'a1','a2','a3'
union all select 'b1','b2','b3'
union all select 'a1','c2','c3'
union all select 'b1','d2','d3'
union all select 'b1','e2','e3'
union all select 'a1','f2','f3'
union all select 'a1','g2','g3'select
id = (
select count(*)
from @t
where F1 = a.F1
and checksum(F2 ,f3)<= checksum(a.F2,a.f3)
),
a.F1,
a.F2,
a.F3
from @t a
order by a.F1,id
/*
id F1 F2 F3
----------- ---- ---- ----
1 a1 a2 a3
2 a1 c2 c3
3 a1 f2 f3
4 a1 g2 g3
1 b1 b2 b3
2 b1 d2 d3
3 b1 e2 e3(7 行受影响)
*/
SELECT ROW_NUMBER() OVER(ORDER BY f2 DESC) AS ID, f1, f2, f3 FROM tb order by id asc
insert into @T
select 'a1','a2','a3' union all
select 'b1','b2','b3' union all
select 'a1','c2','c3' union all
select 'b1','d2','d3' union all
select 'b1','e2','e3' union all
select 'a1','f2','f3' union all
select 'a1','g2','g3'
SELECT DENSE_RANK() OVER (PARTITION BY f1 order by f2) id,f1,f2,f3
FROM @t
/*
1 a1 a2 a3
2 a1 c2 c3
3 a1 f2 f3
4 a1 g2 g3
1 b1 b2 b3
2 b1 d2 d3
3 b1 e2 e3
*/
2,用count计数法,当然,如果要比较的值有重复要稍微处理一下。
参见此文评论补充
http://blog.csdn.net/fcuandy/archive/2007/04/05/1552710.aspx
create table #(F1 varchar(10), F2 varchar(10), F3 varchar(10))
---------------
insert # select 'a1', 'a2' , 'a3'
insert # select 'b1', 'a2' , 'b3'
insert # select 'a1', 'a2' , 'c3'
insert # select 'b1', 'a2' , 'd3'
insert # select 'b1', 'a2' , 'e3'
insert # select 'a1', 'a2' , 'f3'
insert # select 'a1', 'a2' , 'g3'
alter table #
add cnt int identity(1,1)
go
select id=(select count(*)+1 from # where f1=t.f1 and cnt<t.cnt),f1,f2,f3 from # t order by f1,f3
alter table #
drop column cnt
/*
id f1 f2 f3
----------- ---------- ---------- ----------
1 a1 a2 a3
2 a1 a2 c3
3 a1 a2 f3
4 a1 a2 g3
1 b1 a2 b3
2 b1 a2 d3
3 b1 a2 e3
*/