http://community.csdn.net/Expert/topic/5574/5574285.xml?temp=.7109339这是您帮俺做的,可是我要是还想得到他们的差3该怎么办啊???
谢谢谢谢
谢谢谢谢
解决方案 »
- SQL查询效率问题?
- matlab 人脸识别
- 拆解字符串后加[],再合并的问题
- sql语句
- 有什么办法可以在查询时生成一自动增长列?
- [在线]关于从SQL中访问DB2数据库的问题?急,---zjcxc(邹建)请进!
- 与其它数据库连接问题。
- mssql启动代理
- 请问市场上能否买到mssql的盗版软件啊 如果不能请问正版的需要多少钱啊
- 为什么我装完SQL2000,看不到LOCALHOST,而且在SQL SERVER MANGER里面也没有MSSERVER这一项可以运行?
- 建立维护计划时出现错误:无法将 NULL 值插入列 'owner_sid',表 'msdb.dbo.sysjobs';该列不允许空值。INSERT 失败。
- 给一个列限定格式
(id Int,
dt Datetime,
data Int)
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
/*
id dt today9 tomorrow9 差
1 2007-05-01 09:00:10.000 100 103 3
*/
(id Int,
dt Datetime,
data Int)
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
/*
id dt today9 tomorrow9 差
1 2007-05-01 09:00:10.000 100 103 3
2 2007-05-01 09:00:10.000 101 106 5
*/