各位怎么取出一个月有多少天

Question

我要取出一月有多少天,然后把#员工表里面的员工编号及员工姓名添加日期表里面create table  #员工表(员工编号 int,姓名 nvarchar(50))
insert into #员工表 values (001,'张三')
insert into #员工表 values (002,'李四')
create table  #日期表(员工编号 int,姓名 nvarchar(50),日期 datetime)
--想要的结果
/*
员工编号        姓名                                                 日期
----------- -------------------------------------------------- -----------------------
1           张三                                                 2011-05-01 00:00:00.000
2           李四                                                 2011-05-01 00:00:00.000
1           张三                                                 2011-05-02 00:00:00.000
2           李四                                                 2011-05-02 00:00:00.000
...............此处省略1           张三                                                 2011-05-31 00:00:00.000
2           李四                                                 2011-05-31 00:00:00.000
*/
如果已经存在了,不能再添加也就是不能再重复.

Answer 1

create table  #员工表(员工编号 int,姓名 nvarchar(50))
insert into #员工表 values (001,'张三')
insert into #员工表 values (002,'李四')
create table  #日期表(员工编号 int,姓名 nvarchar(50),日期 datetime)insert into #日期表
select 员工编号,姓名,dateadd(dd,number,'2011-05-01')
from master..spt_values,#员工表
where type='p' and dateadd(dd,number,'2011-05-01')<'2011-06-01'
order by 3,1select * from #日期表/*
员工编号        姓名                                                 日期
----------- -------------------------------------------------- -----------------------
1           张三                                                 2011-05-01 00:00:00.000
2           李四                                                 2011-05-01 00:00:00.000
1           张三                                                 2011-05-02 00:00:00.000
2           李四                                                 2011-05-02 00:00:00.000
1           张三                                                 2011-05-03 00:00:00.000
2           李四                                                 2011-05-03 00:00:00.000
1           张三                                                 2011-05-04 00:00:00.000
2           李四                                                 2011-05-04 00:00:00.000
1           张三                                                 2011-05-05 00:00:00.000
....

Answer 2

insert into #日期表
select 员工编号,姓名,dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')
from master..spt_values a,#员工表 b
where type='p' 
and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<convert(varchar(8),dateadd(m,1,getdate()),120)+'01'select * from #日期表
/**
员工编号        姓名                                                 日期
----------- -------------------------------------------------- -----------------------
1           张三                                                 2011-05-01 00:00:00.000
2           李四                                                 2011-05-01 00:00:00.000
1           张三                                                 2011-05-02 00:00:00.000
2           李四                                                 2011-05-02 00:00:00.000
1           张三                                                 2011-05-03 00:00:00.000
2           李四                                                 2011-05-03 00:00:00.000
1           张三                                                 2011-05-04 00:00:00.000
2           李四                                                 2011-05-04 00:00:00.000
1           张三                                                 2011-05-05 00:00:00.000
2           李四                                                 2011-05-05 00:00:00.000
1           张三                                                 2011-05-06 00:00:00.000
2           李四                                                 2011-05-06 00:00:00.000
1           张三                                                 2011-05-07 00:00:00.000
2           李四                                                 2011-05-07 00:00:00.000
1           张三                                                 2011-05-08 00:00:00.000
2           李四                                                 2011-05-08 00:00:00.000
1           张三                                                 2011-05-09 00:00:00.000
2           李四                                                 2011-05-09 00:00:00.000
1           张三                                                 2011-05-10 00:00:00.000
2           李四                                                 2011-05-10 00:00:00.000
1           张三                                                 2011-05-11 00:00:00.000
2           李四                                                 2011-05-11 00:00:00.000
1           张三                                                 2011-05-12 00:00:00.000
2           李四                                                 2011-05-12 00:00:00.000
1           张三                                                 2011-05-13 00:00:00.000
2           李四                                                 2011-05-13 00:00:00.000
1           张三                                                 2011-05-14 00:00:00.000
2           李四                                                 2011-05-14 00:00:00.000
1           张三                                                 2011-05-15 00:00:00.000
2           李四                                                 2011-05-15 00:00:00.000
1           张三                                                 2011-05-16 00:00:00.000
2           李四                                                 2011-05-16 00:00:00.000
1           张三                                                 2011-05-17 00:00:00.000
2           李四                                                 2011-05-17 00:00:00.000
1           张三                                                 2011-05-18 00:00:00.000
2           李四                                                 2011-05-18 00:00:00.000
1           张三                                                 2011-05-19 00:00:00.000
2           李四                                                 2011-05-19 00:00:00.000
1           张三                                                 2011-05-20 00:00:00.000
2           李四                                                 2011-05-20 00:00:00.000
1           张三                                                 2011-05-21 00:00:00.000
2           李四                                                 2011-05-21 00:00:00.000
1           张三                                                 2011-05-22 00:00:00.000
2           李四                                                 2011-05-22 00:00:00.000
1           张三                                                 2011-05-23 00:00:00.000
2           李四                                                 2011-05-23 00:00:00.000
1           张三                                                 2011-05-24 00:00:00.000
2           李四                                                 2011-05-24 00:00:00.000
1           张三                                                 2011-05-25 00:00:00.000
2           李四                                                 2011-05-25 00:00:00.000
1           张三                                                 2011-05-26 00:00:00.000
2           李四                                                 2011-05-26 00:00:00.000
1           张三                                                 2011-05-27 00:00:00.000
2           李四                                                 2011-05-27 00:00:00.000
1           张三                                                 2011-05-28 00:00:00.000
2           李四                                                 2011-05-28 00:00:00.000
1           张三                                                 2011-05-29 00:00:00.000
2           李四                                                 2011-05-29 00:00:00.000
1           张三                                                 2011-05-30 00:00:00.000
2           李四                                                 2011-05-30 00:00:00.000
1           张三                                                 2011-05-31 00:00:00.000
2           李四                                                 2011-05-31 00:00:00.000(62 行受影响)
**/

Answer 3

;with cte1 as
(
select 员工编号,姓名,dateadd(dd,number,convert(varchar(8),getdate(),120)+'01') as 日期
from master..spt_values a,#员工表 b
where type='p' 
and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<convert(varchar(8),dateadd(m,1,getdate()),120)+'01'
)
insert into #日期表
select * from cte1 t
where not exists(select 1 from #日期表 where 员工编号=t.员工编号 and 姓名=t.姓名 and 日期=t.日期)

Answer 4

--已经存在了,不能再添加也就是不能再重复.这个帮我判断一下
insert into #日期表
select 员工编号,姓名,dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')
from master..spt_values a,#员工表 b
where type='p' 
and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<convert(varchar(8),dateadd(m,1,getdate()),120)+'01'
and (dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')) not in (select 日期 from #日期表)

Answer 5

谢谢各位这次又学到了不少知识.真是高手如云master..spt
第一次见到这个,帮忙讲讲