如有一个表
工号 拉号 入厂日期 离职日期
01 A1 2010/10/01 2010/10/10
02 A1 2010/10/01 2010/10/05
03 A2 2010/10/02 2010/10/05
有一个WEB页面,输入日期范围2010/10/02—2010/10/06
怎样用SQL语句显示以下资料
日期 拉号 员工总数
2010/10/02 A1 2
2010/10/02 A2 1
2010/10/03 A1 2
2010/10/03 A2 1
2010/10/04 A1 2
2010/10/04 A2 1
2010/10/05 A1 2
2010/10/05 A2 1
2010/10/06 A1 1
2010/10/06 A2 0
工号 拉号 入厂日期 离职日期
01 A1 2010/10/01 2010/10/10
02 A1 2010/10/01 2010/10/05
03 A2 2010/10/02 2010/10/05
有一个WEB页面,输入日期范围2010/10/02—2010/10/06
怎样用SQL语句显示以下资料
日期 拉号 员工总数
2010/10/02 A1 2
2010/10/02 A2 1
2010/10/03 A1 2
2010/10/03 A2 1
2010/10/04 A1 2
2010/10/04 A2 1
2010/10/05 A1 2
2010/10/05 A2 1
2010/10/06 A1 1
2010/10/06 A2 0
解决方案 »
- 数据库编程:数据库导入的步骤谁能给解释下?
- 怎样查到到底哪条是重复记录?(所有字段全一样)
- 求一看似简单实不简单的SQL语句
- 安装sql server2005时,提示“无法启动SQL Server(MSSQLSERVER)服务 错误1053“和“sqlservr.exe[4076] 中发生未处理的win32异常”
- 菜鸟问SQL语句
- 某日期('2004-03-31')的下一个月的天数(30天)和下一年(2005-03-31)的天数(364)的求法!!
- update 的Sql语句,就10分了
- 求SQL语句,表A一条数据对表B多条数据, 根据表B的条件筛选出表A
- SQL2005储存过程里varchar(8000)字符变量远远超过8000怎么办?求助
- 为什么sql server2000服务器端装不上去啊????
- 请教如何求两列的平均值?
- 让表行列互转的问题
select 拉号,count(1) 员工总数 from T where '2010/10/02' between 入厂日期 and 离职日期 group by 拉号
统计一个日期范围的没好办法,得用循环了
declare @d datetime
set @d='2010/10/02'
while @d<'2010/10/06'
begin
select @d 日期,拉号,count(1) 员工总数 from T where @d between 入厂日期 and 离职日期 group by 拉号
select @d=dateadd(day,1,@d)
end
create table #tb (工号 varchar(10),拉号 varchar(10),入厂日期 datetime,离厂日期 datetime)
insert #tb
select '01','A1', '2010/10/01', '2010/10/10' union all
select '02' ,'A1' ,'2010/10/01', '2010/10/05' union all
select '03', 'A2', '2010/10/02', '2010/10/05'
declare @startdate datetime
declare @enddate datetime
set @startdate = '2010-10-02'
set @enddate = '2010-10-06'
select dateadd(dd,number,@startdate),拉号,count(*) from #tb, master..spt_values
where type = 'p' and dateadd(dd,number,@startdate) <= @enddate group by 拉号,number/*结果
2010-10-02 00:00:00.000 A1 2
2010-10-02 00:00:00.000 A2 1
2010-10-03 00:00:00.000 A1 2
2010-10-03 00:00:00.000 A2 1
2010-10-04 00:00:00.000 A1 2
2010-10-04 00:00:00.000 A2 1
2010-10-05 00:00:00.000 A1 2
2010-10-05 00:00:00.000 A2 1
2010-10-06 00:00:00.000 A1 2
2010-10-06 00:00:00.000 A2 1*/
INSERT @a SELECT '01','A1','2010/10/01','2010/10/10'
union all select '02','A1','2010/10/01','2010/10/05'
union all select '03','A2','2010/10/02','2010/10/05'DECLARE @x1 SMALLDATETIME,@x2 SMALLDATETIME
SELECT @x1='2010-10-02',@x2='2010-10-06'SELECT x,lh,SUM(CASE WHEN x BETWEEN rcrc AND lzrc THEN 1 ELSE 0 END) zs
FROM (SELECT dateadd(day,number,@x1) x FROM MASTER.dbo.spt_values
WHERE TYPE='P' AND number BETWEEN 0 AND DATEDIFF(DAY,@x1,@x2))aa,@a bb
GROUP BY x,lh
ORDER BY x
/*
x lh zs
------------------------------------------------------ -------------------- -----------
2010-10-02 00:00:00 A1 2
2010-10-02 00:00:00 A2 1
2010-10-03 00:00:00 A1 2
2010-10-03 00:00:00 A2 1
2010-10-04 00:00:00 A1 2
2010-10-04 00:00:00 A2 1
2010-10-05 00:00:00 A1 2
2010-10-05 00:00:00 A2 1
2010-10-06 00:00:00 A1 1
2010-10-06 00:00:00 A2 0(所影响的行数为 10 行)
*/
where type = 'p' and (dateadd(dd,number,@startdate) <= @enddate) and
(dateadd(dd,number,@startdate) between 入厂日期 and 离厂日期)
group by 拉号,number
create table V(id varchar(3),na varchar(3),startt datetime,endt datetime)insert V
select '01', 'A1', '2010/10/01', '2010/10/10' union all
select '02', 'A1', '2010/10/01', '2010/10/05' union all
select '03', 'A2', '2010/10/02', '2010/10/05'*/select a.riqi,V.na,num=count(V.na) from
(select
riqi=dateadd(dd,number,'2010-10-2')
from
master..spt_values
where
type='p'
and
dateadd(dd,number,'2010-10-2')<='2010-10-6')a,V
where a.riqi between V.startt and V.endt
group by na,a.riqi
/*
2010-10-02 00:00:00.000 A1 2
2010-10-02 00:00:00.000 A2 1
2010-10-03 00:00:00.000 A1 2
2010-10-03 00:00:00.000 A2 1
2010-10-04 00:00:00.000 A1 2
2010-10-04 00:00:00.000 A2 1
2010-10-05 00:00:00.000 A1 2
2010-10-05 00:00:00.000 A2 1
2010-10-06 00:00:00.000 A1 1
*/
where b.type='p' and dateadd(dd,b.number,'2010-10-02') <='2010-10-06'
and dateadd(dd,b.number,'2010-10-02') between 入厂日期 and 离厂日期
group by 拉号,number