create table S(
Code varchar(50),
Status int)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('1',5)
insert into ServiceRequest values ('2',5)
insert into ServiceRequest values ('2',5)
insert into ServiceRequest values ('2',5)
insert into ServiceRequest values ('2',5)
create table W(id int,
Code varchar(50),
Date datetime)
insert into WorkList values (1,'1','2009-09-01')
insert into WorkList values (2,'2','2009-09-02')
insert into WorkList values (3,'3','2009-09-03')
insert into WorkList values (4,'4','2009-09-04')
insert into WorkList values (5,'5','2009-09-05')
insert into WorkList values (6,'6','2009-09-06')
insert into WorkList values (7,'7','2009-09-07')
insert into WorkList values (8,'8','2009-09-08')
insert into WorkList values (9,'9','2009-09-09')
insert into WorkList values (10,'10','2009-09-10')
insert into WorkList values (11,'11','2009-09-11')
go--求 按月统计 表S 中 code 条数。--我写的
select Date,(select count(w.Code) from W as w,S as s
where w.Code=s.Code and s.Status='0'
) as cnt
from W where Date > dateadd(dd,-day('2009-9-15')+1,'2009-9-15') and
cWorkDate < dateadd(dd,-day(dateadd(m,1,'2009-9-15')),dateadd(m,1,'2009-9-15'))
--2009-09-02 00:00:00.000 14
--2009-09-03 00:00:00.000 14
--2009-09-04 00:00:00.000 14
--2009-09-05 00:00:00.000 14
--2009-09-06 00:00:00.000 14
--2009-09-07 00:00:00.000 14
--2009-09-08 00:00:00.000 14
--2009-09-09 00:00:00.000 14
--2009-09-10 00:00:00.000 14
--2009-09-11 00:00:00.000 14
--结果 不是按日统计的
Code varchar(50),
Status int)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('2',0)
insert into S values ('2',0)
insert into S values ('2',0)
insert into S values ('2',0)
create table W(id int,
Code varchar(50),
Date datetime)
insert into W values (1,'1','2009-09-01')
insert into W values (2,'2','2009-09-02')
insert into W values (3,'3','2009-09-03')
insert into W values (4,'4','2009-09-04')
insert into W values (5,'5','2009-09-05')
insert into W values (6,'6','2009-09-06')
insert into W values (7,'7','2009-09-07')
insert into W values (8,'8','2009-09-08')
insert into W values (9,'9','2009-09-09')
insert into W values (10,'10','2009-09-10')
insert into W values (11,'11','2009-09-11')
go--求 按月统计 表S 中 code 条数。select Date,(select count(w.Code) from W as w,S as s
where w.Code=s.Code and s.Status='0'
) as cnt
from W where Date > dateadd(dd,-day('2009-9-15')+1,'2009-9-15') and
Date < dateadd(dd,-day(dateadd(m,1,'2009-9-15')),dateadd(m,1,'2009-9-15'))
drop table W,S
convert(varchar(7),Date,120) as Date,(select count(w.Code) from W as w,S as s
where w.Code=s.Code and s.Status='0'
) as cnt
from
W
where
Date > dateadd(dd,-day('2009-9-15')+1,'2009-9-15')
and
Date < dateadd(dd,-day(dateadd(m,1,'2009-9-15')),dateadd(m,1,'2009-9-15'))
group by
convert(varchar(7),Date,120) as Date
convert(varchar(7),Date,120) as Date,(select count(w.Code) from W as w,S as s
where w.Code=s.Code and s.Status='0'
) as cnt
from
W
where
Date > dateadd(dd,-day('2009-9-15')+1,'2009-9-15')
and
Date < dateadd(dd,-day(dateadd(m,1,'2009-9-15')),dateadd(m,1,'2009-9-15'))
group by
convert(varchar(7),Date,120)
Code varchar(50),
Status int)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('2',0)
insert into S values ('2',0)
insert into S values ('2',0)
insert into S values ('2',0)
create table W(id int,
Code varchar(50),
Date datetime)
insert into W values (1,'1','2009-09-01')
insert into W values (2,'2','2009-09-02')
insert into W values (3,'3','2009-09-03')
insert into W values (4,'4','2009-09-04')
insert into W values (5,'5','2009-09-05')
insert into W values (6,'6','2009-09-06')
insert into W values (7,'7','2009-09-07')
insert into W values (8,'8','2009-09-08')
insert into W values (9,'9','2009-09-09')
insert into W values (10,'10','2009-09-10')
insert into W values (11,'11','2009-09-11')
go--求 按月统计 表S 中 code 条数。select distinct mm=convert(varchar(7),Date,120),(select count(w.Code) from W as w,S as s
where w.Code=s.Code and s.Status='0'
) as cnt
from W where Date > dateadd(dd,-day('2009-9-15')+1,'2009-9-15') and
Date < dateadd(dd,-day(dateadd(m,1,'2009-9-15')),dateadd(m,1,'2009-9-15'))
drop table W,S/*
mm cnt
------- -----------
2009-09 14(所影响的行数为 1 行)*/
/*
date cnt
------------------------------------------------------ -----------
2009-09-01 00:00:00.000 10
2009-09-02 00:00:00.000 4
2009-09-03 00:00:00.000 0
2009-09-04 00:00:00.000 0
2009-09-05 00:00:00.000 0
2009-09-06 00:00:00.000 0
2009-09-07 00:00:00.000 0
2009-09-08 00:00:00.000 0
2009-09-09 00:00:00.000 0
2009-09-10 00:00:00.000 0
2009-09-11 00:00:00.000 0(所影响的行数为 11 行)
*/如果还需要什么条件,自己加上即可.
是这样的,先按月统计表W中的 数据,就是1到月末,然后按日统计S表中的code。
结果应该是
2009-9-1 2
2009-9-2 5
2009-9-3 10
这样的效果。
Code varchar(50),
Status int)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('1',0)
insert into S values ('2',0)
insert into S values ('2',0)
insert into S values ('2',0)
insert into S values ('2',0)
create table W(id int,
Code varchar(50),
Date datetime)
insert into W values (1,'1','2009-09-01')
insert into W values (2,'2','2009-09-02')
insert into W values (3,'3','2009-09-03')
insert into W values (4,'4','2009-09-04')
insert into W values (5,'5','2009-09-05')
insert into W values (6,'6','2009-09-06')
insert into W values (7,'7','2009-09-07')
insert into W values (8,'8','2009-09-08')
insert into W values (9,'9','2009-09-09')
insert into W values (10,'10','2009-09-10')
insert into W values (11,'11','2009-09-11')
goselect w.id,convert(varchar(7),w.Date,120),isnull(count(s.code),0) as num
from s
left join
w
on
s.code=w.code
group by
convert(varchar(7),w.Date,120),w.iddrop table s,w
/*id num
----------- ------- -----------
1 2009-09 10
2 2009-09 4(2 行受影响)*/
2009-09-03 是W中的时间, 7 是 S表中 count(Code)的值