解决方案 »
- 不好意思,还要问问,"海阔天空"给了答案但是与我的实际不太符合,怎么修改???
- sql server dts包 疑问 在线等
- 0403161731转换成日期2004-03-16 17:31,有个什么转换函数,或者什么别方法?
- 海量数据写入与查询?
- 不用MSDE的安装包实现SQL桌面版部署与启动服务的方法
- 请教:服务器为何每天下午变慢?!!!!
- 如何实现商品进出库管理?软件和硬件。谢谢。
- 一个少见的问题,请高手进入!!修改表结构时,怎么样触发另外一事件?在线等待!!
- 请问如何写一个存储过程获取某个表的主码,关键是过程的语法...
- 大虾们,初学者问题,请教了!
- sqlserver2005 sp3 X64安装的时候windows installer.msp安装不上
- 查询垃圾用户
FROM
(SELECT a.Location, a.[Date], DateDiff(minute, a.[Date], b.[Date]) AS Stay
FROM CTE_Log a INNER JOIN CTE_Log b ON a.RN = b.RN-1 ) S
GROUP BY S.Location
create table travel
(location varchar(10),[date] varchar(10))insert into travel
select 'PRC','20100101' union all
select 'PRC','20100102' union all
select 'PRC','20100104' union all
select 'USA','20100110' union all
select 'USA','20100118' union all
select 'UK','20100120' union all
select 'UK','20100120' union all
select 'UK','20100123' union all
select 'DE','20100124' union all
select 'FR','20100125' union all
select 'FR','20100201' union all
select 'PRC','20100203' union all
select 'PRC','20100203' union all
select 'TW','20100205'
with t as
(select location,[date],row_number() over(order by [date]) 'rn'
from travel),
u as
(select a.location,a.[date],row_number() over(order by a.rn) 'rn'
from t a
left join t b on a.rn=b.rn+1
where a.location!=b.location or b.rn is null)
select location,sum(d) 'days'
from
(select a.location,isnull(datediff(d,a.[date],b.[date]),1) 'd'
from u a
left join u b on a.rn=b.rn-1) t
group by location
order by (select min(rn) from t c where c.location=t.location)/*
location days
---------- -----------
PRC 11
USA 10
UK 4
DE 1
FR 9
TW 1(6 row(s) affected)
*/