create table tb(日期 datetime, 员工号 varchar(10)) insert into tb values('2009/08/03' , '00005') insert into tb values('2009/08/04' , '00005') insert into tb values('2009/08/07' , '00005') insert into tb values('2009/08/10' , '00005') insert into tb values('2009/08/12' , '00005') insert into tb values('2009/08/13' , '00005') goSET DATEFIRST 7declare @sdate datetime declare @edate datetime set @sdate = '2009-08-01 00:00:000' set @edate = '2009-08-15 00:00:000'select t1.* from ( select m.员工号 , n.dt from (select distinct 员工号 from tb ) m, ( select dateadd(dd,num,@sdate) dt from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate ) n ) t1 where datepart(weekday,t1.dt) not in (1,7) and not exists(select 1 from tb where 员工号 = t1.员工号 and 日期 = t1.dt )drop table tb/* 员工号 dt ---------- ------------------------------------------------------ 00005 2009-08-05 00:00:00.000 00005 2009-08-06 00:00:00.000 00005 2009-08-11 00:00:00.000 00005 2009-08-14 00:00:00.000(所影响的行数为 4 行) */
@@DATEFIRST 返回 SET DATEFIRST 参数的当前值,SET DATEFIRST 参数指明所规定的每周第一天:1 对应星期一,2 对应星期二,依次类推,用 7 对应星期日。语法 @@DATEFIRST返回类型 tinyint注释 美国英语中默认 7 对应星期日。示例 下面的示例将每周第一天设为 5 (星期五),并假定当日是星期六。SELECT 语句返回 DATEFIRST 值和当日是此周的第几天。SET DATEFIRST 5 SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'下面是结果集。从星期五算起,今天(星期六)是第二天。1st Day Today ---------------- -------------- 5 2
那能再解释一下 select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t 这一句么
insert into tb values('2009/08/03' , '00005')
insert into tb values('2009/08/04' , '00005')
insert into tb values('2009/08/07' , '00005')
insert into tb values('2009/08/10' , '00005')
insert into tb values('2009/08/12' , '00005')
insert into tb values('2009/08/13' , '00005')
goSET DATEFIRST 7declare @sdate datetime
declare @edate datetime
set @sdate = '2009-08-01 00:00:000'
set @edate = '2009-08-15 00:00:000'select t1.* from
(
select m.员工号 , n.dt from
(select distinct 员工号 from tb ) m,
(
select
dateadd(dd,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate
) n
) t1 where datepart(weekday,t1.dt) not in (1,7) and not exists(select 1 from tb where 员工号 = t1.员工号 and 日期 = t1.dt )drop table tb/*
员工号 dt
---------- ------------------------------------------------------
00005 2009-08-05 00:00:00.000
00005 2009-08-06 00:00:00.000
00005 2009-08-11 00:00:00.000
00005 2009-08-14 00:00:00.000(所影响的行数为 4 行)
*/
返回 SET DATEFIRST 参数的当前值,SET DATEFIRST 参数指明所规定的每周第一天:1 对应星期一,2 对应星期二,依次类推,用 7 对应星期日。语法
@@DATEFIRST返回类型
tinyint注释
美国英语中默认 7 对应星期日。示例
下面的示例将每周第一天设为 5 (星期五),并假定当日是星期六。SELECT 语句返回 DATEFIRST 值和当日是此周的第几天。SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'下面是结果集。从星期五算起,今天(星期六)是第二天。1st Day Today
---------------- --------------
5 2
select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t
这一句么