34,575
社区成员
发帖
与我相关
我的任务
分享
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-24 00:39:55
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:TAB1
IF NOT OBJECT_ID('[TAB1]') IS NULL
DROP TABLE [TAB1]
GO
CREATE TABLE [TAB1]([站点] NVARCHAR(10),[上一时次] INT,[下一时次] INT,[时效] NVARCHAR(10),[时间] DATETIME)
INSERT [TAB1]
SELECT N'白沙',10,8,N'24小时',N'2008-04-22 05:00:00.000' UNION ALL
SELECT N'白沙',6,5,N'48小时',N'2008-04-22 05:00:00.000' UNION ALL
SELECT N'白沙',3,4,N'72小时',N'2008-04-22 05:00:00.000' UNION ALL
SELECT N'保亭',2,4,N'24小时',N'2008-04-22 05:00:00.000' UNION ALL
SELECT N'保亭',7,9,N'48小时',N'2008-04-22 05:00:00.000' UNION ALL
SELECT N'保亭',1,12,N'72小时',N'2008-04-22 05:00:00.000'
GO
--SELECT * FROM [TAB1]
-->SQL查询如下:
;with t as
(
select rn=row_number()over(partition by [站点] order by [时效]),*
from [TAB1]
)
select a.站点,b.下一时次 上一时次,a.上一时次 下一时次,
b.时效,时间=DATEADD(HH,min(left(b.时效,2))over(partition by b.站点)*1,b.时间)
from t a
join t b
on a.rn=b.rn+1 and a.站点=b.站点
/*
站点 上一时次 下一时次 时效 时间
---------- ----------- ----------- ---------- -----------------------
白沙 8 6 24小时 2008-04-23 05:00:00.000
白沙 5 3 48小时 2008-04-23 05:00:00.000
保亭 4 7 24小时 2008-04-23 05:00:00.000
保亭 9 1 48小时 2008-04-23 05:00:00.000
(4 行受影响)
*/
select
a.站点,b.下一时次 as 上一时次,a.上一时次 as 下一时次,a.时效 ,a.时间
from
(select id=row_number()over(partition by 站点 order by getdate()),* from tb)a
INNER JOIN
(select id=row_number()over(partition by 站点 order by getdate()),* from tb)b
on a.站点=b.站点 AND a.id-1=b.id
create table [tb](id int,[站点] varchar(4),[上一时次] int,[下一时次] int,[时效] varchar(6),[时间] datetime)
insert [tb]
select 1,'白沙',10,8,'24小时','2008-04-22 05:00:00.000' union all
select 2,'白沙',6,5,'48小时','2008-04-22 05:00:00.000' union all
select 3,'白沙',3,4,'72小时','2008-04-22 05:00:00.000' union all
select 1,'保亭',2,4,'24小时','2008-04-22 05:00:00.000' union all
select 2,'保亭',7,9,'48小时','2008-04-22 05:00:00.000' union all
select 3,'保亭',1,12,'72小时','2008-04-22 05:00:00.000'
go
select * from
(
select m.站点,
上一时次 = m.下一时次,
下一时次 = (select top 1 上一时次 from tb n where n.站点 = n.站点 and n.id > m.id order by id),
时效,
时间 = dateadd(hh,cast(replace(时效, '小时','') as int), (select top 1 时间 from tb n where n.站点 = n.站点 order by id))
from tb m
) t
where 下一时次 is not null
drop table tb
/*
站点 上一时次 下一时次 时效 时间
---- ----------- ----------- ------ ------------------------------------------------------
白沙 8 6 24小时 2008-04-23 05:00:00.000
白沙 5 1 48小时 2008-04-24 05:00:00.000
保亭 4 6 24小时 2008-04-23 05:00:00.000
保亭 9 1 48小时 2008-04-24 05:00:00.000
(所影响的行数为 4 行)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-23 23:04:54
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([站点] varchar(4),[上一时次] int,[下一时次] int,[时效] varchar(6),[时间] datetime)
insert [tb]
select '白沙',10,8,'24小时','2008-04-22 05:00:00.000' union all
select '白沙',6,5,'48小时','2008-04-22 05:00:00.000' union all
select '白沙',3,4,'72小时','2008-04-22 05:00:00.000' union all
select '保亭',2,4,'24小时','2008-04-22 05:00:00.000' union all
select '保亭',7,9,'48小时','2008-04-22 05:00:00.000' union all
select '保亭',1,12,'72小时','2008-04-22 05:00:00.000'
--------------开始查询--------------------------
select
a.站点,b.下一时次 as 上一时次,a.上一时次 as 下一时次,a.时效 ,a.时间
from
(select id=row_number()over(partition by 站点 order by getdate()),* from tb)a,
(select id=row_number()over(partition by 站点 order by getdate()),* from tb)b
where
a.id-1=b.id
and
a.站点=b.站点
----------------结果----------------------------
/* 站点 上一时次 下一时次 时效 时间
---- ----------- ----------- ------ -----------------------
白沙 8 6 48小时 2008-04-22 05:00:00.000
白沙 5 3 72小时 2008-04-22 05:00:00.000
保亭 4 7 48小时 2008-04-22 05:00:00.000
保亭 9 1 72小时 2008-04-22 05:00:00.000
(4 行受影响)
*/
SELECT t1.站点,
t1.[下一时次] [上一时次], t2.[上一时次] [下一时次],
t1.时效, t1.时间
FROM tab t1 inner join tab t2
ON t1.时效+24=t2.时效 and t1.站点=t2.站点