在sql2000中,row_number函数不支持?怎么处理
在sql2000中,row_number函数不支持??怎么办
这是前几天论坛上的高人给写的程序,里面的row_number函数不能用,求等效的语句
------解决方案--------------------
利用中间表的identity列,或是count计数法。
see
http://blog.csdn.net/fcuandy/archive/2007/04/05/1552710.aspx
及评论补充
这是前几天论坛上的高人给写的程序,里面的row_number函数不能用,求等效的语句
- SQL code
IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta Go CREATE TABLE ta(Family NVARCHAR(4),Member INT,activity NVARCHAR(8),starttime varchar(10),endtime varchar(10)) Go INSERT INTO ta SELECT 'A-01',1,'sleeping','0:00','6:30' UNION ALL SELECT 'A-01',1,'shower','6:30','6:45' UNION ALL SELECT 'A-01',1,'work','8:00','12:00' UNION ALL SELECT 'A-01',1,'read','12:00','23:59' UNION ALL SELECT 'A-01',2,'sleeping','0:00','6:30' UNION ALL SELECT 'A-01',2,'shower','6:30','6:45' UNION ALL SELECT 'A-01',2,'work','8:00','12:00' UNION ALL SELECT 'A-01',2,'eat','12:00','13:00' UNION ALL SELECT 'A-01',2,'work','13:00','20:00' UNION ALL SELECT 'A-02',1,'sleeping','0:00','6:30' UNION ALL SELECT 'A-02',1,'shower','6:30','6:45' UNION ALL SELECT 'A-02',1,'work','8:00','12:00' GO --Start SELECT ltrim(rowid)+'.00' as [hour],family,member, (select top 1 activity from ta where family = a.family and cast( '2008-01-01 '+ltrim(rowid)+':01' as datetime) between dateadd(mi,1,cast('2008-01-01 '+starttime as datetime)) and cast('2008-01-01 '+endtime as datetime)) as activity FROM (select distinct family ,member from ta) a cross join (select rowid = row_number() over (order by object_id) from sys.objects ) b where rowid between 6 and 23 order by family,member
------解决方案--------------------
利用中间表的identity列,或是count计数法。
see
http://blog.csdn.net/fcuandy/archive/2007/04/05/1552710.aspx
及评论补充