像我这样写,可是就只能查到有数据的,如果没有数据的就显示不出来了。能不能帮我改一下 SELECT A.WorkID, Count(B.CustomerID) as Count From ReceiveRecord as A LEFT Join ReceiveRecord as B On A.WorkID = B.WorkID WHERE (A.WorkID = 'FS00000001' OR A.WorkID = 'FS00000002') And B.DateTime BETWEEN '2009-01-11 00:00:00' AND '2009-01-11 23:59:59' GROUP BY A.WorkID ORDER BY Count ASC
if object_id('tempdb..#')is not null drop table # go create table #(ID int, 用户名 varchar(10), 事件 int, 时间 datetime) insert # select 1, 'FS00000001', 4 ,'1985-06-11 02:01:56.000' insert # select 2, 'FS00000001' , 4 ,'1985-06-11 02:01:56.000' insert # select 3, 'FS00000002' , 4, '1985-06-11 02:01:56.000' select count(*)[count],用户名 from # where datediff(dd,时间,'1985-06-11')=0 group by 用户名 /*count 用户名 ----------- ---------- 2 FS00000001 1 FS00000002*/ select a.用户名,a.时间 ,sum(case when b.事件 is not null then 1 else 0 end)[count] from ( select 'FS00000001' 用户名 ,'1985-06-12' 时间 union all select 'FS00000002' ,'1985-06-12' union all select 'FS00000001' ,'1985-06-11' union all select 'FS00000002' ,'1985-06-11' )a left join # b on a.用户名=b.用户名 and a.时间=convert(varchar(10),b.时间,120) group by a.用户名,a.时间 /*用户名 时间 count ---------- ---------- ----------- FS00000001 1985-06-11 2 FS00000002 1985-06-11 1 FS00000001 1985-06-12 0 FS00000002 1985-06-12 0*/
中间的这个是什么意思 ( select 'FS00000001' 用户名 ,'1985-06-12' 时间 union all select 'FS00000002' ,'1985-06-12' union all select 'FS00000001' ,'1985-06-11' union all select 'FS00000002' ,'1985-06-11' )
1 FS00000001 4 1985-06-11 02:01:56.000
2 FS00000001 4 1985-06-11 02:01:56.000
3 FS00000002 4 1985-06-11 02:01:56.000
像查询在1985-06-11一天里的数据是:
Count WorkID
1 FS00000002
2 FS00000001
而像我查询1985-06-12时,查出的数据要是这样的
Count WorkID
0 FS00000001
0 FS00000002
SELECT A.WorkID, Count(B.CustomerID) as Count From ReceiveRecord as A LEFT Join ReceiveRecord as B
On A.WorkID = B.WorkID WHERE (A.WorkID = 'FS00000001' OR
A.WorkID = 'FS00000002') And B.DateTime BETWEEN '2009-01-11 00:00:00' AND '2009-01-11 23:59:59'
GROUP BY A.WorkID ORDER BY Count ASC
go
create table #(ID int, 用户名 varchar(10), 事件 int, 时间 datetime)
insert # select 1, 'FS00000001', 4 ,'1985-06-11 02:01:56.000'
insert # select 2, 'FS00000001' , 4 ,'1985-06-11 02:01:56.000'
insert # select 3, 'FS00000002' , 4, '1985-06-11 02:01:56.000'
select count(*)[count],用户名 from # where datediff(dd,时间,'1985-06-11')=0 group by 用户名
/*count 用户名
----------- ----------
2 FS00000001
1 FS00000002*/
select a.用户名,a.时间 ,sum(case when b.事件 is not null then 1 else 0 end)[count] from (
select 'FS00000001' 用户名 ,'1985-06-12' 时间 union all
select 'FS00000002' ,'1985-06-12' union all
select 'FS00000001' ,'1985-06-11' union all
select 'FS00000002' ,'1985-06-11'
)a left join # b
on a.用户名=b.用户名 and a.时间=convert(varchar(10),b.时间,120)
group by a.用户名,a.时间
/*用户名 时间 count
---------- ---------- -----------
FS00000001 1985-06-11 2
FS00000002 1985-06-11 1
FS00000001 1985-06-12 0
FS00000002 1985-06-12 0*/
(
select 'FS00000001' 用户名 ,'1985-06-12' 时间 union all
select 'FS00000002' ,'1985-06-12' union all
select 'FS00000001' ,'1985-06-11' union all
select 'FS00000002' ,'1985-06-11'
)