关于自动编号的SQL（SQL2000）
本帖最后由 qdits 于 2013-11-12 14:38:08 编辑 有这样一组数据
id the_date the_value
-----------------------------------------
1 2013-11-1 A
2 2013-11-1 A
3 2013-11-2 A
4 2013-11-2 A
5 2013-11-2 A
6 2013-11-3 A
7 2013-11-3 A
8 2013-11-4 A
9 2013-11-4 A
10 2013-11-4 A
我想在id后面，根据日期增加一行row_id，如
ps.我不需要在表中增加一个字段，我只需要在view时能查看得到
id row_id the_date the_value
---------------------------------------------------------
1 1 2013-11-1 A
2 2 2013-11-1 A
3 1 2013-11-2 A
4 2 2013-11-2 A
5 3 2013-11-2 A
6 4 2013-11-2 A
7 1 2013-11-3 A
8 1 2013-11-4 A
9 2 2013-11-4 A
10 3 2013-11-4 A
在sql2000环境下该如何写？
[解决办法]
----------------------------------------------------------------
-- Author :TravyLee(走自己的路，让狗去叫吧！)
-- Date :2013-11-12 14:38:00
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[the_date] datetime,[the_value] varchar(1))
insert [tb]
select 1,'2013-11-1','A' union all
select 2,'2013-11-1','A' union all
select 3,'2013-11-2','A' union all
select 4,'2013-11-2','A' union all
select 5,'2013-11-2','A' union all
select 6,'2013-11-3','A' union all
select 7,'2013-11-3','A' union all
select 8,'2013-11-4','A' union all
select 9,'2013-11-4','A' union all
select 10,'2013-11-4','A'
go
select
px=(select count(1) from tb b where a.[the_date]=b.[the_date] and
a.[the_value]=b.[the_value] and a.id>=b.id)
,* from tb a
/*
pxidthe_datethe_value
---------------------------------------------
112013-11-01 00:00:00.000A
222013-11-01 00:00:00.000A
132013-11-02 00:00:00.000A
242013-11-02 00:00:00.000A
352013-11-02 00:00:00.000A
162013-11-03 00:00:00.000A
272013-11-03 00:00:00.000A
182013-11-04 00:00:00.000A
292013-11-04 00:00:00.000A
3102013-11-04 00:00:00.000A
*/
