--sql 2000 select * , px = (select count(1) from tb where 日期 = t.日期 and 姓名 < t.姓名) + 1 from tb t--sql 2005 select * , px = row_number() over(partition by 日期 order by 姓名) from tb t
select 日期,row_number()over(order by 姓名) as '序号',姓名 from 表
-->Title:Generating test data -->Author: -->Date :2009-10-20 15:08:24
if not object_id('Tempdb..#t') is null drop table #t Go Create table #t([日期] Datetime,[姓名] nvarchar(2)) Insert #t select '2009-10-01',N'张三' union all select '2009-10-01',N'李四' union all select '2009-10-02',N'赵六' Go select [日期], 序号=row_number()over(partition by [日期] order by getdate()), [姓名] from #t /* 日期 序号 姓名 ----------------------- -------------------- ---- 2009-10-01 00:00:00.000 1 张三 2009-10-01 00:00:00.000 2 李四 2009-10-02 00:00:00.000 1 赵六 */
---2000求排名的参考 --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([id] int,[point] int,[name] varchar(4)) insert [TB] select 1,100,'张三' union all select 2,80,'李四' union all select 3,80,'王五' union all select 4,75,'杨六' union all select 5,75,'方七'select id=(select count([point])+1 from TB where T.[point]<[point]),point,name from TB t/* id point name ----------- ----------- ---- 1 100 张三 2 80 李四 2 80 王五 4 75 杨六 4 75 方七(5 行受影响) */drop table TB--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([id] int,[point] int,[name] Nvarchar(4)) insert [TB] select 1,100,N'张三' union all select 2,80,N'李四' union all select 3,80,N'王五' union all select 4,75,N'杨六' union all select 5,75,N'方七'select id=(select count(distinct point) from TB where T.[point]<=[point]), point, [name] from TB t /* id point name ----------- ----------- ---- 1 100 张三 2 80 李四 2 80 王五 3 75 杨六 3 75 方七(5 個資料列受到影響) */
select * , px = (select count(1) from tb where 日期 = t.日期 and 姓名 < t.姓名) + 1 from tb t--sql 2005
select * , px = row_number() over(partition by 日期 order by 姓名) from tb t
-->Author:
-->Date :2009-10-20 15:08:24
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([日期] Datetime,[姓名] nvarchar(2))
Insert #t
select '2009-10-01',N'张三' union all
select '2009-10-01',N'李四' union all
select '2009-10-02',N'赵六'
Go
select
[日期],
序号=row_number()over(partition by [日期] order by getdate()),
[姓名]
from #t
/*
日期 序号 姓名
----------------------- -------------------- ----
2009-10-01 00:00:00.000 1 张三
2009-10-01 00:00:00.000 2 李四
2009-10-02 00:00:00.000 1 赵六
*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[point] int,[name] varchar(4))
insert [TB]
select 1,100,'张三' union all
select 2,80,'李四' union all
select 3,80,'王五' union all
select 4,75,'杨六' union all
select 5,75,'方七'select id=(select count([point])+1 from TB where T.[point]<[point]),point,name
from TB t/*
id point name
----------- ----------- ----
1 100 张三
2 80 李四
2 80 王五
4 75 杨六
4 75 方七(5 行受影响)
*/drop table TB--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[point] int,[name] Nvarchar(4))
insert [TB]
select 1,100,N'张三' union all
select 2,80,N'李四' union all
select 3,80,N'王五' union all
select 4,75,N'杨六' union all
select 5,75,N'方七'select id=(select count(distinct point) from TB where T.[point]<=[point]),
point,
[name]
from TB t
/*
id point name
----------- ----------- ----
1 100 张三
2 80 李四
2 80 王五
3 75 杨六
3 75 方七(5 個資料列受到影響)
*/
INSERT @t VALUES('2009-10-01','张三')
INSERT @t VALUES('2009-10-01','李四')
INSERT @t VALUES('2009-10-02','王五')
INSERT @t VALUES('2009-10-02','赵六')SELECT 日期,序号=ROW_NUMBER() OVER(PARTITION BY 日期 ORDER BY 姓名),姓名
FROM @t/*
日期 序号 姓名
----------------------- -------------------- --------------------
2009-10-01 00:00:00.000 1 李四
2009-10-01 00:00:00.000 2 张三
2009-10-02 00:00:00.000 1 王五
2009-10-02 00:00:00.000 2 赵六
*/