求一SQL语句

[dj3688]

工作日表,结构如下:

create table workdate([date] int)
insert into workdate
select 20090803
union 
select 20090804
union  
select 20090805
union 
select 20090806
union 
select 20090807
union 
select 20090810
union 
select 20090811
/*需返回
date 	area
20090803 20090803
20090803	20090804
20090803	20090805
20090804	20090804
20090804	20090805
20090804	20090806
20090805	20090805
20090805	20090806
20090805	20090807
20090806	20090806
20090806	20090807
20090806	20090810
20090807	20090807
20090807	20090810
20090807	20090811
20090810 20090810
20090810 20090811
20090810 20090812
...
*/

area为后续三个工作日,以20090807为例,为20090807,20090810,20090811
有现成的函数有供调阅
create function getworkdate(@workdate int)
returns @temp table(area int)
as
begin
insert @temp select top 3 [date] from workdate where [date]>=@workdate
return
end

[灬上海爽爷]

up，学习一下

[xupeihuagudulei]

[Quote=引用 1 楼 tcwsyt 的回复:]
up，学习一下
[/Quote]顶，坐等牛人

[华夏小卒]

if object_id('workdate')is not null drop table workdate
go
create table workdate([date] int)
insert into workdate
select 20090803
union 
select 20090804
union  
select 20090805
union 
select 20090806
union 
select 20090807
union 
select 20090810
union 
select 20090811

select date,id=identity(int,1,1) into #t from workdate

select t.date,area=b.date from #t t ,#t b
where t.id=b.id or t.id=b.id-1 or t.id=b.id-2
order by t.date,b.date

drop table #t

date        area
----------- -----------
20090803    20090803
20090803    20090804
20090803    20090805
20090804    20090804
20090804    20090805
20090804    20090806
20090805    20090805
20090805    20090806
20090805    20090807
20090806    20090806
20090806    20090807
20090806    20090810
20090807    20090807
20090807    20090810
20090807    20090811
20090810    20090810
20090810    20090811
20090811    20090811

[liangCK]

create table workdate([date] int)
insert into workdate
select 20090803
union 
select 20090804
union  
select 20090805
union 
select 20090806
union 
select 20090807
union 
select 20090810
union 
select 20090811
GO

SELECT *
FROM (SELECT CONVERT(VARCHAR(10),date) AS date FROM workdate) AS A
    CROSS APPLY (SELECT TOP(3) DATEADD(day,number,A.date) AS area
                 FROM master.dbo.spt_values 
                 WHERE type = 'p' AND number < 5
                     AND DATEPART(weekday,DATEADD(day,number,A.date)) NOT IN(1,7)) AS B

GO
DROP TABLE workdate

/*
date       area
---------- -----------------------
20090803   2009-08-03 00:00:00.000
20090803   2009-08-04 00:00:00.000
20090803   2009-08-05 00:00:00.000
20090804   2009-08-04 00:00:00.000
20090804   2009-08-05 00:00:00.000
20090804   2009-08-06 00:00:00.000
20090805   2009-08-05 00:00:00.000
20090805   2009-08-06 00:00:00.000
20090805   2009-08-07 00:00:00.000
20090806   2009-08-06 00:00:00.000
20090806   2009-08-07 00:00:00.000
20090806   2009-08-10 00:00:00.000
20090807   2009-08-07 00:00:00.000
20090807   2009-08-10 00:00:00.000
20090807   2009-08-11 00:00:00.000
20090810   2009-08-10 00:00:00.000
20090810   2009-08-11 00:00:00.000
20090810   2009-08-12 00:00:00.000
20090811   2009-08-11 00:00:00.000
20090811   2009-08-12 00:00:00.000
20090811   2009-08-13 00:00:00.000

(21 行受影响)
*/

[liangCK]

没考虑节假日的.

[华夏小卒]

if object_id('workdate')is not null drop table workdate
go
create table workdate([date] int)
insert into workdate
select 20090803
union 
select 20090804
union  
select 20090805
union 
select 20090806
union 
select 20090807
union 
select 20090810
union 
select 20090811

--->SQL2005
;with szy as
(
select date,id=row_number()over(order by getdate())
from workdate
)

select t.date,area=b.date from szy t ,szy b
where t.id=b.id or t.id=b.id-1 or t.id=b.id-2
order by t.date,b.date

date        area
----------- -----------
20090803    20090803
20090803    20090804
20090803    20090805
20090804    20090804
20090804    20090805
20090804    20090806
20090805    20090805
20090805    20090806
20090805    20090807
20090806    20090806
20090806    20090807
20090806    20090810
20090807    20090807
20090807    20090810
20090807    20090811
20090810    20090810
20090810    20090811
20090811    20090811

[xupeihuagudulei]

怎么有些看不懂，为什么这版的SQL都挺少看懂，晕

[liangCK]

汗.看错.后续的工作日直接在workdate表里获取.

[dj3688]

[Quote=引用 8 楼 liangck 的回复:]
汗.看错.后续的工作日直接在workdate表里获取.
[/Quote]

是的.

3楼的应该就可以

谢谢大家

[黄_瓜]

来晚了

[guguda2008]

直接TOP3 WHERE DATE> ORDER就行了吧

[chuifengde]

DECLARE @a table ([date] int)
insert into @a
select 20090803
union 
select 20090804
union  
select 20090805
union 
select 20090806
union 
select 20090807
union 
select 20090810
union 
select 20090811


SELECT * FROM (
SELECT a.date d1,b.date d2 FROM @a a,@a b 
)aa where d2 IN(SELECT TOP 3 date FROM @a where [date]>=aa.[d1] ORDER BY 1)
ORDER BY d1

--result
/*
d1          d2          
----------- ----------- 
20090803    20090803
20090803    20090804
20090803    20090805
20090804    20090804
20090804    20090805
20090804    20090806
20090805    20090805
20090805    20090806
20090805    20090807
20090806    20090806
20090806    20090807
20090806    20090810
20090807    20090807
20090807    20090810
20090807    20090811
20090810    20090810
20090810    20090811
20090811    20090811

（所影响的行数为 18 行）*/

[jiangshun]

create table workdate([date] int)
insert into workdate
select 20090803
union 
select 20090804
union  
select 20090805
union 
select 20090806
union 
select 20090807
union 
select 20090810
union 
select 20090811


select date,area=date+(Row_Number() over(PARTITION BY date order by date))-1 from (
select * from workdate union all
select * from workdate union all
select * from workdate)t order by date
/*
date        area
----------- --------------------
20090803    20090803
20090803    20090804
20090803    20090805
20090804    20090804
20090804    20090805
20090804    20090806
20090805    20090805
20090805    20090806
20090805    20090807
20090806    20090806
20090806    20090807
20090806    20090808
20090807    20090807
20090807    20090808
20090807    20090809
20090810    20090810
20090810    20090811
20090810    20090812
20090811    20090811
20090811    20090812
20090811    20090813

(21 行受影响)


*/


drop table workdate