首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

一天到晚游泳的鱼,再帮俺一次吧,该怎么处理

2012-01-24 
一天到晚游泳的鱼,再帮俺一次吧http://community.csdn.net/Expert/topic/5574/5574285.xml?temp.7109339

一天到晚游泳的鱼,再帮俺一次吧
http://community.csdn.net/Expert/topic/5574/5574285.xml?temp=.7109339

这是您帮俺做的,可是我要是还想得到他们的差3该怎么办啊???
谢谢谢谢

[解决办法]
let 's go
[解决办法]
支持
[解决办法]
Create Table A
(idInt,
dtDatetime,
dataInt)
Insert A Select 1, '2007-05-1 9:00:10 ', 100
Union All Select 1, '2007-05-1 9:04:10 ', 101
Union All Select 1, '2007-05-1 9:08:10 ', 102
Union All Select 1, '2007-05-2 9:01:10 ', 103
Union All Select 1, '2007-05-2 9:05:18 ', 104
GO
Select
*,
tomorrow9 - today9 As 差
From
(
Select
TOP 1
id,
dt,
data As today9,
(Select TOP 1 data From A Where id = T.id And DateDiff(dd, T.dt, dt) = 1 And Datepart(hh, dt) > =9 Order By dt) As tomorrow9
From A T
Where Datepart(hh, dt) > =9 And DateDiff(dd, dt, ' 2007-05-01 ') = 0
Order By dt
) A
GO
Drop Table A
--Result
/*
iddttoday9tomorrow9差
12007-05-01 09:00:10.0001001033
*/


[解决办法]
我以為你只要一行數據叻,稍微修改下。

Create Table A
(idInt,
dtDatetime,
dataInt)
Insert A Select 1, '2007-05-1 9:00:10 ', 100
Union All Select 1, '2007-05-1 9:04:10 ', 101
Union All Select 1, '2007-05-1 9:08:10 ', 102
Union All Select 1, '2007-05-2 9:01:10 ', 103
Union All Select 1, '2007-05-2 9:05:18 ', 104
Union All Select 2, '2007-05-1 9:00:10 ', 101
Union All Select 2, '2007-05-1 9:04:10 ', 102
Union All Select 2, '2007-05-1 9:08:10 ', 103
Union All Select 2, '2007-05-2 9:01:10 ', 106
GO
Select
*,
tomorrow9 - today9 As 差
From
(
Select
id,
dt,
data As today9,
(Select TOP 1 data From A Where id = T.id And DateDiff(dd, T.dt, dt) = 1 And Datepart(hh, dt) > =9 Order By dt) As tomorrow9
From A T
Where DateDiff(dd, dt, ' 2007-05-01 ') = 0 And dt = (Select TOP 1 dt From A Where id = T.id And DateDiff(dd, T.dt, dt) = 0 And Datepart(hh, dt) > =9 Order By dt)
) A
Order By id, dt
GO
Drop Table A
--Result
/*
iddttoday9tomorrow9差
12007-05-01 09:00:10.0001001033
22007-05-01 09:00:10.0001011065
*/

热点排行