工作日表,结构如下: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
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
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 20090811select date,id=identity(int,1,1) into #t from workdateselect 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.datedrop table #tdate 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
insert into workdate
select 20090803
union
select 20090804
union
select 20090805
union
select 20090806
union
select 20090807
union
select 20090810
union
select 20090811
GOSELECT *
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 BGO
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 行受影响)
*/
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.datedate 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
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 行)*/
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