变量1 等于 2010-1-3 (开始日期)
变量2 等于 2010-1-5 (结束日期)变量3 等于 0000,0001,0002 (工号)那么批量生成一下数据:
工号 日期
0000 2010-1-3
0000 2010-1-4
0000 2010-1-5
0001 2010-1-3
0001 2010-1-4
0001 2010-1-5
0002 2010-1-3
0002 2010-1-4
0002 2010-1-5
请注意一下,是变量,不要做成死的,谢谢。
变量2 等于 2010-1-5 (结束日期)变量3 等于 0000,0001,0002 (工号)那么批量生成一下数据:
工号 日期
0000 2010-1-3
0000 2010-1-4
0000 2010-1-5
0001 2010-1-3
0001 2010-1-4
0001 2010-1-5
0002 2010-1-3
0002 2010-1-4
0002 2010-1-5
请注意一下,是变量,不要做成死的,谢谢。
日期
from(
select dateadd(day,number,'2010-1-3')日期
from master..spt_values
where type='P' and dateadd(day,number,'2010-1-3')<='2010-1-5')a,
(select '0000'工号
union all
select '0001'
union all
select '0002')b
order by 工号
/*
工号 日期
---- -----------------------
0000 2010-01-03 00:00:00.000
0000 2010-01-04 00:00:00.000
0000 2010-01-05 00:00:00.000
0001 2010-01-03 00:00:00.000
0001 2010-01-04 00:00:00.000
0001 2010-01-05 00:00:00.000
0002 2010-01-03 00:00:00.000
0002 2010-01-04 00:00:00.000
0002 2010-01-05 00:00:00.000
*/
SELECT @Dt1='2010-1-3',@Dt2='2010-1-5',@s='0000,0001,0002'
;WITH Cte
AS
(
SELECT @Dt1 AS dt
UNION ALL
SELECT Dt+1 FROM Cte WHERE Dt<@Dt2
)select
b.COl2 AS 工序,dt AS 日期
from
(select Dt,COl2=convert(xml,'<root><v>'+replace(@s,',','</v><v>')+'</v></root>') from Cte)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
ORDER BY b.Col2/*
工序 日期
0000 2010-01-03 00:00:00.000
0000 2010-01-04 00:00:00.000
0000 2010-01-05 00:00:00.000
0001 2010-01-05 00:00:00.000
0001 2010-01-04 00:00:00.000
0001 2010-01-03 00:00:00.000
0002 2010-01-03 00:00:00.000
0002 2010-01-04 00:00:00.000
0002 2010-01-05 00:00:00.000
*/
DECLARE @var2 VARCHAR(10)
DECLARE @var3 VARCHAR(50)SET @var1='2010-01-03'
SET @var2='2010-01-05'
SET @var3='0000,0001,0002'SELECT 工号,日期
FROM
(
SELECT DATEADD(dd,number,@var1) AS 日期
FROM MASTER.dbo.spt_values sv
WHERE sv.number BETWEEN 0 AND (DATEDIFF(dd,@var1,@var2))
AND sv.[type]='P'
)M
CROSS JOIN(SELECT A AS 工号 FROM dbo.fn_split(@var3,',')) N工号 日期
------------------------------------------
0000 2010-01-03 00:00:00.000
0000 2010-01-04 00:00:00.000
0000 2010-01-05 00:00:00.000
0001 2010-01-03 00:00:00.000
0001 2010-01-04 00:00:00.000
0001 2010-01-05 00:00:00.000
0002 2010-01-03 00:00:00.000
0002 2010-01-04 00:00:00.000
0002 2010-01-05 00:00:00.000(9 row(s) affected)
create function [dbo].[fn_split](@inputstr nvarchar(4000), @seprator varchar(10))
returns @temp table (a nvarchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
declare @id1 nvarchar(10),@id2 nvarchar(10),@id3 nvarchar(10)
set @dt1='2010-1-3'
set @dt2='2010-1-5'
set @id1='0000'
set @id2='0001'
set @id3='0002';with cte as(
select @dt1 as dt
union all
select dateadd(dd,1,a.dt)as dt from cte a where dateadd(dd,1,a.dt)<=@dt2
)select a1,dt from cte,
(
select @id1 as a1
union all
select @id2
union all
select @id3
)t
order by a1,dt
/*
a1 dt
---------- -----------------------
0000 2010-01-03 00:00:00.000
0000 2010-01-04 00:00:00.000
0000 2010-01-05 00:00:00.000
0001 2010-01-03 00:00:00.000
0001 2010-01-04 00:00:00.000
0001 2010-01-05 00:00:00.000
0002 2010-01-03 00:00:00.000
0002 2010-01-04 00:00:00.000
0002 2010-01-05 00:00:00.000(9 行受影响)*/
DECLARE @TODATE DATETIME
DECLARE @STR NVARCHAR(MAX)
DECLARE @TEMP NVARCHAR(MAX)
DECLARE @COUNT INT
DECLARE @RESULT TABLE(ID VARCHAR(5),DATE DATETIME)
SET @FROMDATE='2010-01-01'
SET @TODATE='2010-01-10'
SET @STR='0000,0001,0003'+','
WHILE CHARINDEX(',',@STR)>0
BEGIN
SELECT @COUNT=DATEDIFF(DD,@FROMDATE,@TODATE)+1
SELECT @TEMP=LEFT(@STR,CHARINDEX(',',@STR)-1)
WHILE @COUNT>0
BEGIN
INSERT INTO @RESULT
SELECT @TEMP,DATEADD(DD,@COUNT-1,@FROMDATE)
SET @COUNT=@COUNT-1
END SELECT @STR=STUFF(@STR,1,CHARINDEX(',',@STR),'')END
SELECT * FROM @RESULT---------------------------
ID DATE
0000 2010-01-10 00:00:00.000
0000 2010-01-09 00:00:00.000
0000 2010-01-08 00:00:00.000
0000 2010-01-07 00:00:00.000
0000 2010-01-06 00:00:00.000
0000 2010-01-05 00:00:00.000
0000 2010-01-04 00:00:00.000
0000 2010-01-03 00:00:00.000
0000 2010-01-02 00:00:00.000
0000 2010-01-01 00:00:00.000
0001 2010-01-10 00:00:00.000
0001 2010-01-09 00:00:00.000
0001 2010-01-08 00:00:00.000
0001 2010-01-07 00:00:00.000
0001 2010-01-06 00:00:00.000
0001 2010-01-05 00:00:00.000
0001 2010-01-04 00:00:00.000
0001 2010-01-03 00:00:00.000
0001 2010-01-02 00:00:00.000
0001 2010-01-01 00:00:00.000
0003 2010-01-10 00:00:00.000
0003 2010-01-09 00:00:00.000
0003 2010-01-08 00:00:00.000
0003 2010-01-07 00:00:00.000
0003 2010-01-06 00:00:00.000
0003 2010-01-05 00:00:00.000
0003 2010-01-04 00:00:00.000
0003 2010-01-03 00:00:00.000
0003 2010-01-02 00:00:00.000
0003 2010-01-01 00:00:00.000