昨天的一个问题，原来我表达错了，高手在帮帮忙吧~

Question

表结构是这样的：id                         url                                                               time
377 http://item.taobao.com/auction/item_detail-0db2.jhtml?cm_cat=50012027 2009-04-20 01:21:20.000
378 http://item.taobao.com/auction/item_detail-db2-352bcf428eb2901.jhtml 2009-04-20 01:26:26.000
379 http://item.taobao.com/auction/item_detail-0db2-09ad1.jhtml?cm_cat=0 2009-04-20 01:54:39.000
380 http://item.taobao.com/auction/item_detail-080.jhtml?cm_cat=50006843 2009-04-20 02:03:37.000
380 http://store.taobao.com/shop/view_shop-ba90d1f498c3a776e3c75b3c5.htm 2009-04-20 02:03:41.000
380 http://store.taobao.com/shop/xshop/wui_page-cat-1321231333653752.htm 2009-04-20 02:03:48.000
380 http://shop33653752.taobao.com/?orderType=_time&viewType=null&is=abc    2009-04-20 02:04:00.000
380 http://shop33653752.taobao.com/?keyword=null&orderType=_time&viewTyp 2009-04-20 02:04:07.000
380 http://shop33653752.taobao.com/?scid=null&keyword=null&orderType=123 2009-04-20 02:04:16.000
380 http://shop33653752.taobao.com/?pageNum=5&scid=null&keyword=null&n=1 2009-04-20 02:04:22.000
380 http://item.taobao.com/auction/item_detail-0db2-e10d3c4962a35a23.htm 2009-04-20 02:04:36.000我的问题是：先得到相同id的记录按时间倒叙排序，然后得到  点击某个页面的时间和下次点击不同页面时间的时间差（暂时算做是页面的停留时间，公司领导先让这么算，虽然不准）结果像这样：
380 http://item.taobao.com/auction/item_detail-0db2-e10d3c4962a35a23.htm null （这条是最大的时间，下次点击时间还没有，所以是null）
380 http://shop33653752.taobao.com/?pageNum=5&scid=null&keyword=null&n=1 14‘
380 http://shop33653752.taobao.com/?scid=null&keyword=null&orderType=123 8‘
380 http://shop33653752.taobao.com/?keyword=null&orderType=_time&viewTyp 9’请求帮忙写个存储过程~~（写的可能有点乱，请包涵，相信大家应该可以理解）

Answer 1

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-05-09 12:15:59
---------------------------------
 
IF OBJECT_ID('[tc]') IS NOT NULL 
    DROP TABLE [tc]
go
CREATE TABLE [tc] (id INT,url VARCHAR(69),time DATETIME)
INSERT INTO [tc]
SELECT 377,'http://item.taobao.com/auction/item_detail-0db2.jhtml?cm_cat=50012027','2009-04-20 01:21:20.000' UNION ALL
SELECT 378,'http://item.taobao.com/auction/item_detail-db2-352bcf428eb2901.jhtml','2009-04-20 01:26:26.000' UNION ALL
SELECT 379,'http://item.taobao.com/auction/item_detail-0db2-09ad1.jhtml?cm_cat=0','2009-04-20 01:54:39.000' UNION ALL
SELECT 380,'http://item.taobao.com/auction/item_detail-080.jhtml?cm_cat=50006843','2009-04-20 02:03:37.000' UNION ALL
SELECT 380,'http://store.taobao.com/shop/view_shop-ba90d1f498c3a776e3c75b3c5.htm','2009-04-20 02:03:41.000' UNION ALL
SELECT 380,'http://store.taobao.com/shop/xshop/wui_page-cat-1321231333653752.htm','2009-04-20 02:03:48.000' UNION ALL
SELECT 380,'http://shop33653752.taobao.com/?orderType=_time&viewType=null&is=abc','2009-04-20 02:04:00.000' UNION ALL
SELECT 380,'http://shop33653752.taobao.com/?keyword=null&orderType=_time&viewTyp','2009-04-20 02:04:07.000' UNION ALL
SELECT 380,'http://shop33653752.taobao.com/?scid=null&keyword=null&orderType=123','2009-04-20 02:04:16.000' UNION ALL
SELECT 380,'http://shop33653752.taobao.com/?pageNum=5&scid=null&keyword=null&n=1','2009-04-20 02:04:22.000' UNION ALL
SELECT 380,'http://item.taobao.com/auction/item_detail-0db2-e10d3c4962a35a23.htm','2009-04-20 02:04:36.000'select * from [tc]with
wang as( select row=row_number() over (partition by id order by [time] desc),* from tc)select s.id,s.url,s.time,时间差=datediff(ss,s.time,t.time)
from wang s left join wang t on s.row=t.row+1 and s.id=t.idid url time 时间差
377 http://item.taobao.com/auction/item_detail-0db2.jhtml?cm_cat=50012027 2009-04-20 01:21:20.000 NULL
378 http://item.taobao.com/auction/item_detail-db2-352bcf428eb2901.jhtml 2009-04-20 01:26:26.000 NULL
379 http://item.taobao.com/auction/item_detail-0db2-09ad1.jhtml?cm_cat=0 2009-04-20 01:54:39.000 NULL
380 http://item.taobao.com/auction/item_detail-0db2-e10d3c4962a35a23.htm 2009-04-20 02:04:36.000 NULL
380 http://shop33653752.taobao.com/?pageNum=5&scid=null&keyword=null&n=1 2009-04-20 02:04:22.000 14
380 http://shop33653752.taobao.com/?scid=null&keyword=null&orderType=123 2009-04-20 02:04:16.000 6
380 http://shop33653752.taobao.com/?keyword=null&orderType=_time&viewTyp 2009-04-20 02:04:07.000 9
380 http://shop33653752.taobao.com/?orderType=_time&viewType=null&is=abc 2009-04-20 02:04:00.000 7
380 http://store.taobao.com/shop/xshop/wui_page-cat-1321231333653752.htm 2009-04-20 02:03:48.000 12
380 http://store.taobao.com/shop/view_shop-ba90d1f498c3a776e3c75b3c5.htm 2009-04-20 02:03:41.000 7
380 http://item.taobao.com/auction/item_detail-080.jhtml?cm_cat=50006843 2009-04-20 02:03:37.000 4

Answer 2

declare @t table(id int,url varchar(300),dt datetime)
INSERT INTO @t
SELECT 
377 ,'http://item.taobao.com/auction/item_detail-0db2.jhtml?cm_cat=50012027', '2009-04-20 01:21:20.000'
UNION ALL SELEcT
378 ,'http://item.taobao.com/auction/item_detail-db2-352bcf428eb2901.jhtml' ,'2009-04-20 01:26:26.000'
UNION ALL SELEcT
379 ,'http://item.taobao.com/auction/item_detail-0db2-09ad1.jhtml?cm_cat=0' ,'2009-04-20 01:54:39.000'
UNION ALL SELEcT
380 ,'http://item.taobao.com/auction/item_detail-080.jhtml?cm_cat=50006843' ,'2009-04-20 02:03:37.000'
UNION ALL SELEcT
380 ,'http://store.taobao.com/shop/view_shop-ba90d1f498c3a776e3c75b3c5.htm' ,'2009-04-20 02:03:41.000'
UNION ALL SELEcT
380 ,'http://store.taobao.com/shop/xshop/wui_page-cat-1321231333653752.htm' ,'2009-04-20 02:03:48.000'
UNION ALL SELEcT
380 ,'http://shop33653752.taobao.com/?orderType=_time&viewType=null&is=abc' ,   '2009-04-20 02:04:00.000'
UNION ALL SELEcT
380 ,'http://shop33653752.taobao.com/?keyword=null&orderType=_time&viewTyp' ,'2009-04-20 02:04:07.000'
UNION ALL SELEcT
380 ,'http://shop33653752.taobao.com/?scid=null&keyword=null&orderType=123' ,'2009-04-20 02:04:16.000'
UNION ALL SELEcT
380 ,'http://shop33653752.taobao.com/?pageNum=5&scid=null&keyword=null&n=1' ,'2009-04-20 02:04:22.000'
UNION ALL SELEcT
380 ,'http://item.taobao.com/auction/item_detail-0db2-e10d3c4962a35a23.htm' ,'2009-04-20 02:04:36.000' SELECT id,url,dt,'diff(second)'=datediff(second,a.dt,(select top 1 dt from @t where id=a.id and dt>a.dt ))
FROM @t a
order by id,dt desc

Answer 3

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[url] varchar(69),[time] datetime)
insert [tb]
select 377,'http://item.taobao.com/auction/item_detail-0db2.jhtml?cm_cat=50012027','2009-04-20 01:21:20.000' union all
select 378,'http://item.taobao.com/auction/item_detail-db2-352bcf428eb2901.jhtml','2009-04-20 01:26:26.000' union all
select 379,'http://item.taobao.com/auction/item_detail-0db2-09ad1.jhtml?cm_cat=0','2009-04-20 01:54:39.000' union all
select 380,'http://item.taobao.com/auction/item_detail-080.jhtml?cm_cat=50006843','2009-04-20 02:03:37.000' union all
select 380,'http://store.taobao.com/shop/view_shop-ba90d1f498c3a776e3c75b3c5.htm','2009-04-20 02:03:41.000' union all
select 380,'http://store.taobao.com/shop/xshop/wui_page-cat-1321231333653752.htm','2009-04-20 02:03:48.000' union all
select 380,'http://shop33653752.taobao.com/?orderType=_time&viewType=null&is=abc','2009-04-20 02:04:00.000' union all
select 380,'http://shop33653752.taobao.com/?keyword=null&orderType=_time&viewTyp','2009-04-20 02:04:07.000' union all
select 380,'http://shop33653752.taobao.com/?scid=null&keyword=null&orderType=123','2009-04-20 02:04:16.000' union all
select 380,'http://shop33653752.taobao.com/?pageNum=5&scid=null&keyword=null&n=1','2009-04-20 02:04:22.000' union all
select 380,'http://item.taobao.com/auction/item_detail-0db2-e10d3c4962a35a23.htm','2009-04-20 02:04:36.000'select
  a.id,
  a.url,
  a.[time],
  datediff(ss,a.[time],b.[time])
from
  (select *,px=(select count(1)+1 from tb where id=t.id and [time]>t.[time]) from tb t) a
left join
  (select *,px=(select count(1)+1 from tb where id=t.id and [time]>t.[time]) from tb t) b
on
  a.id=b.id and a.px=b.px+1
order by
  a.id,
  a.[time] desc--测试结果:
/*
id          url                                                                   time                                                               
----------- --------------------------------------------------------------------- ------------------------------------------------------ ----------- 
377         http://item.taobao.com/auction/item_detail-0db2.jhtml?cm_cat=50012027 2009-04-20 01:21:20.000                                NULL
378         http://item.taobao.com/auction/item_detail-db2-352bcf428eb2901.jhtml  2009-04-20 01:26:26.000                                NULL
379         http://item.taobao.com/auction/item_detail-0db2-09ad1.jhtml?cm_cat=0  2009-04-20 01:54:39.000                                NULL
380         http://item.taobao.com/auction/item_detail-0db2-e10d3c4962a35a23.htm  2009-04-20 02:04:36.000                                NULL
380         http://shop33653752.taobao.com/?pageNum=5&scid=null&keyword=null&n=1  2009-04-20 02:04:22.000                                14
380         http://shop33653752.taobao.com/?scid=null&keyword=null&orderType=123  2009-04-20 02:04:16.000                                6
380         http://shop33653752.taobao.com/?keyword=null&orderType=_time&viewTyp  2009-04-20 02:04:07.000                                9
380         http://shop33653752.taobao.com/?orderType=_time&viewType=null&is=abc  2009-04-20 02:04:00.000                                7
380         http://store.taobao.com/shop/xshop/wui_page-cat-1321231333653752.htm  2009-04-20 02:03:48.000                                12
380         http://store.taobao.com/shop/view_shop-ba90d1f498c3a776e3c75b3c5.htm  2009-04-20 02:03:41.000                                7
380         http://item.taobao.com/auction/item_detail-080.jhtml?cm_cat=50006843  2009-04-20 02:03:37.000                                4（所影响的行数为 11 行）
*/