急！大哥们来帮忙，关于SQL查询
表A：
agentid starttime result areaname
100 2007-8-22 8:23 21 hangzhou
101 2007-8-22 8:24 11 hangzhou
101 2007-8-23 8:31 1 hangzhou
101 2007-8-27 17:25 123 NULL
101 2007-8-27 17:32 123 NULL
101 2007-8-27 17:33 456 NULL
100 2007-8-22 8:23 2 hangzhou
101 2007-8-22 8:24 1 hangzhou
101 2007-8-23 8:31 1 hangzhou
101 2007-8-27 17:25 3 NULL
101 2007-8-27 17:32 5 NULL
101 2007-8-27 17:33 3 NULL
100 2007-8-22 8:23 2 hangzhou
100 2007-8-22 8:23 2 hangzhou
100 2007-8-22 8:23 2 hangzhou
101 2007-8-22 8:24 1 hangzhou
101 2007-8-23 8:31 111 hangzhou
101 2007-8-27 17:25 4
101 2007-8-27 17:32 41
101 2007-8-27 17:33 456
102 NULL NULL NULL
101 2007-8-25 8:24 1 hangzhou
100 2007-8-22 8:24 11 hangzhou
100 2007-8-22 8:24 11 hangzhou
表B：
yewu result
业务1 3
业务2 1
业务3 4
业务4 2
业务5 5
业务6 7
业务7 0
业务8 8
我现在想查询每天表A中result字段不在表B中的个数，但是希望能把表A中的每天都显示出来。如果某一天表A中的result字段在表B中，则查询出来的个数为0，请大家帮忙。我的查询语句如下： 
select count(A.result),convert(char(8),starttime,112) from A where A.result not in(select B.result from B) group by convert(char(8),starttime,112),agentid,areaname order by convert(char(8),starttime,112) desc 
查询出来的结果为：
5 20070827
1 20070823
3 20070822
1 20070822
但中间有个20070825这天的没查出来。因为我想得到
5 20070827
0 20070825
1 20070823
3 20070822
1 20070822
各位高手帮忙来看下







------解决方案--------------------

SQL code

select sum(case when b.result is null then 1 else 0 end),
convert(char(8),a.starttime,112)       
from A left join b on a.result=b.result
group by convert(char(8),starttime,112),agentid,areaname
order by convert(char(8),starttime,112) desc

------解决方案--------------------
--试试这个？

SQL code

select count(A.result),convert(char(8),a.starttime,112)
from A left join (select B.result from B) b on a.result = b.result
where b.result is null
group by  convert(char(8),a.starttime,112),a.agentid,a.areaname
order by  convert(char(8),a.starttime,112)       desc

------解决方案--------------------
SQL code

--创建环境
create table a
(
agentid varchar(5),
starttime smalldatetime,
result varchar(10),
areaname varchar(20) 
)

insert into a select '100',   '2007-8-22 8:23',     '21',         'hangzhou'   
insert into a select '101',   '2007-8-22 8:24',     '11',         'hangzhou'   
insert into a select '101',   '2007-8-23 8:31',    '1'  ,         'hangzhou'   
insert into a select '101',   '2007-8-27 17:25',   '123' ,      NULL   
insert into a select '101',   '2007-8-27 17:32',   '123' ,      NULL   
insert intol