1. 给定一个日期范围（一般是一个月，比如2009-03-01——2009-03-31），如何产生这样一个表 beg1 end1 beg2 end2 beg3 end3 beg4 end4 beg5 end5 beg6 end6
2009-02-23 2009-03-01 2009-03-02 2009-03-08 2009-03-09 2009-03-15……beg固定是周一，end固定是周日2.如何随机删除记录，比如beg1,end1范围内，满足flag=1的记录有三条，我要随机删除其中两条，只保留一条，请给个例子
2009-02-23 2009-03-01 2009-03-02 2009-03-08 2009-03-09 2009-03-15……beg固定是周一，end固定是周日2.如何随机删除记录，比如beg1,end1范围内，满足flag=1的记录有三条，我要随机删除其中两条，只保留一条，请给个例子
测试数据不是给了吗，2009-03-01 2009-03-31，结果也给了几周的了
create proc sp_createtable
@begdate datetime,
@enddate datetime
as
set nocount on
select id=identity(int,1,1) into # from sysobjects
declare @s varchar(8000)
if object_id('tb') is not null drop table tb --这步会删除表,注意实际需要
set @s='create table tb ('
select @s=@s+quotename(convert(varchar(10),dateadd(dd,id-1,@begdate),120))+' varchar(20),'
from # where id<=datediff(dd,@begdate,@enddate)+1
select @s=left(@s,len(@s)-1)+')'
exec(@s)
drop table #
goexec sp_createtable '2009-03-01','2009-03-31'
select * from tb
create table test(
beg1 datetime,end1 datetime,beg2 datetime,end2 datetime,beg3 datetime,end3 datetime,
beg4 datetime,end4 datetime,beg5 datetime,end5 datetime,beg6 datetime,end6 datetime)
goset datefirst 1declare @st datetime,@et datetime
select @st='2009-03-01',@et='2009-03-31'declare @t table(dt datetime)
insert into @t
select
dateadd(dd,num-7,@st)
from
(select num=(select count(1) from sysobjects where id<t.id) from sysobjects t) a
where
dateadd(dd,num-7,@st)<=dateadd(dd,5,@et)insert into test
select
max(case when datediff(ww,b.dt,a.dt)=0 and datepart(dw,a.dt)=1 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=1 and datepart(dw,a.dt)=7 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=1 and datepart(dw,a.dt)=1 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=2 and datepart(dw,a.dt)=7 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=2 and datepart(dw,a.dt)=1 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=3 and datepart(dw,a.dt)=7 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=3 and datepart(dw,a.dt)=1 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=4 and datepart(dw,a.dt)=7 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=4 and datepart(dw,a.dt)=1 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=5 and datepart(dw,a.dt)=7 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=5 and datepart(dw,a.dt)=1 then a.dt end),
max(case when datediff(ww,b.dt,a.dt)=6 and datepart(dw,a.dt)=7 then a.dt end)
from
@t a,(select min(dt) as dt from @t where datepart(dw,dt)=1) b
select * from test
/*
beg1 end1 beg2 end2 beg3 end3 beg4 end4 beg5 end5 beg6 end6
------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
2009-02-23 00:00:00.000 2009-03-01 00:00:00.000 2009-03-02 00:00:00.000 2009-03-08 00:00:00.000 2009-03-09 00:00:00.000 2009-03-15 00:00:00.000 2009-03-16 00:00:00.000 2009-03-22 00:00:00.000 2009-03-23 00:00:00.000 2009-03-29 00:00:00.000 2009-03-30 00:00:00.000 2009-04-05 00:00:00.000
*/
godrop table test
go
select @datebegin='2009-03-01',@dateend ='2009-03-31'insert @tempzjk(beg1)
select DATEADD(Day,1-(DATEPART(Weekday,@datebegin)+@@DATEFIRST-2)%7-1,@datebegin)我现在只知道beg1是这样写，后面的字段不知道该如何写，最好能1次insert,
实在不行，可以update @tempzjk
set @dt1 = '2009-03-01'
declare @dt2 as datetime
set @dt2 = '2009-03-31'
declare @i as int
set @i = 0
declare @j as int
set @j = 0
declare @k as int
set @k = 0
declare @beg as varchar(10)
declare @end as varchar(10)
declare @sql as varchar(8000)
set @sql = 'create table tb('
declare @val as varchar(8000)
set @val = ''
set @i = datepart(week , @dt1)
if datepart(weekday , @dt2) = 1
set @j = datepart(week , @dt2)
else
set @j = datepart(week , @dt2) + 1while @i <= @j
begin
set @beg = convert(varchar(10),DATEADD(wk, DATEDIFF(wk,0,dateadd(day , @k*7 , @dt1)),0) - 7,120)
set @end = convert(varchar(10),DATEADD(wk, DATEDIFF(wk,0,dateadd(day , @k*7 , @dt1)),0) - 1,120)
set @sql = @sql + '[beg' + cast(@k+1 as varchar) + '] datetime,'
+ '[end' + cast(@k+1 as varchar) + '] datetime,'
set @val = @val + '''' + @beg + ''',''' + @end + '''' + ','
set @i = @i + 1
set @k = @k + 1
end
set @sql = left(@sql,len(@sql)-1) + ')'
set @val = left(@val,len(@val)-1) exec(@sql)
exec('insert into tb values('+ @val + ')')select * from tbdrop table tb/*
beg1 end1 beg2 end2 beg3 end3 beg4 end4 beg5 end5 beg6 end6
------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
2009-02-23 00:00:00.000 2009-03-01 00:00:00.000 2009-03-02 00:00:00.000 2009-03-08 00:00:00.000 2009-03-09 00:00:00.000 2009-03-15 00:00:00.000 2009-03-16 00:00:00.000 2009-03-22 00:00:00.000 2009-03-23 00:00:00.000 2009-03-29 00:00:00.000 2009-03-30 00:00:00.000 2009-04-05 00:00:00.000（所影响的行数为 1 行）*/
select '2009-02-23',1,1,0 union
select '2009-02-24',1,1,1 union
select '2009-02-25',1,1,2 union
select '2009-02-26',0,1,3 union
select '2009-02-27',1,1,4 union
select '2009-02-28',0,1,5 union
select '2009-03-06',0,1,6 union
我要的结果: dt是在beg1和end1范围内的，并且flag=1的记录，我只保留一条（最好能随机），其他的不动
select @b='2009-03-01'
,@e='2009-03-31'
declare @bmin datetime,@emax datetime
select @bmin=dateadd(d,datepart(dw,@b)-7,@b)
,@emax=dateadd(d,8-datepart(dw,@e),@e)select @bmin as beg1,@bmin+6 as end1
,@bmin+7 as beg2,@bmin+13 as end2
,@bmin+14 as beg3,@bmin+20 as end3
,@bmin+21 as beg4,@bmin+27 as end4
,@bmin+28 as beg5,@bmin+32 as end5
,@bmin+33 as beg6,@bmin+39 as end6
/*
beg1 end1 beg2 end2 beg3 end3 beg4 end4 beg5 end5 beg6 end6
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2009-02-23 00:00:00.000 2009-03-01 00:00:00.000 2009-03-02 00:00:00.000 2009-03-08 00:00:00.000 2009-03-09 00:00:00.000 2009-03-15 00:00:00.000 2009-03-16 00:00:00.000 2009-03-22 00:00:00.000 2009-03-23 00:00:00.000 2009-03-27 00:00:00.000 2009-03-28 00:00:00.000 2009-04-03 00:00:00.000(1 行受影响)
*/
set @b='2009-3-1'
set @e='2009-3-31'declare @tb table(fd datetime)set DATEFIRST 1print datepart(weekday,@b)
set @b=dateadd(day,1-datepart(weekday,@b),@b)while @b<=@e
begin
insert @tb values (@b)
set @b=@b+6
insert @tb values (@b)
set @b=@b+1
endselect * from @tb这是以行形式存储的，通过游标可以取到@b1..@e6即是列的形式了
2、如果表有一个唯一的fid（id字段最好，不是id字段也行）delete tb where fd between beg1 and end1 and flag=1 and fid not in (
select min(fid) from tb where fd between beg1 and end1 and flag=1
)
你没考虑到 任何一个beg是不能大于 @e 的,且有的月份是没有beg6的