？？请教一个难的统计sql写法？？

[drmy]

现在有两个表
OnlineLog表字段：JID，Role
CurrentTask表字段：CaseNo，JID

其中OnlineLog表里每个JID代表一个人，一个人可能有多个Role，这个表按Role插入记录，有几个Role就插几条记录

数据举例是：

OnlineLog表：
0345 2
0345 4
0345 8

0416 2
0416 4
1550 8

1550 2
1550 4

0328 16

CurrentTask表：
CaseNo1 0345
CaseNo2 1550
CaseNo3 1550
CaseNo4 0416
CaseNo5 0345
CaseNo6 0416
CaseNo7 0345
CaseNo8 0416
CaseNo9 0345

我想得到这样的查询结果，就是OnlineLog里面有的JID以及对应的CurrentTask表里这个JID的CaseNo个数，比如下面的结果：
0345 4
0416 3
1550 2
0328 0

我写的sql总是不能把“0328 0”这条数据打印出来，请问怎么写？
我写的sql是：
Select OnlineLog.JID,count(CurrentTask.caseno) From OnlineLog left join CurrentTask On OnlineLog.JID=CurrentTask.JID Group By OnlineLog.JID

[IwantFlay]

用union试一下.

[BES]

select a.JID,Sum(Isnull(a.C_Count,0)) as caseno from
(Select OnlineLog.JID,Case when Isnull(CurrentTask,'')='' then 0 else 1 end as C_Count From OnlineLog left join CurrentTask On OnlineLog.JID=CurrentTask.JID) a
Group By a.JID

[wdsimon]

建立测试表，输入测试数据：
create table OnlineLog(JID,char(8),Role,char(3))
insert into OnlineLog values('0345','2')
insert into OnlineLog values('0345','4')
insert into OnlineLog values('0345','8')
insert into OnlineLog values('0416','2')
insert into OnlineLog values('0416','4')
insert into OnlineLog values('1550','8')
insert into OnlineLog values('1550','2')
insert into OnlineLog values('1550','4')
insert into OnlineLog values('0328','16')
create table CurrentTask(CaseNo char(10),JID char(8))
insert into CurrentTask values('CaseNo1','0345')
insert into CurrentTask values('CaseNo2','1550')
insert into CurrentTask values('CaseNo3','1550')
insert into CurrentTask values('CaseNo4','0416')
insert into CurrentTask values('CaseNo5','0345')
insert into CurrentTask values('CaseNo6','0416')
insert into CurrentTask values('CaseNo7','0345')
insert into CurrentTask values('CaseNo8','0416')
insert into CurrentTask values('CaseNo9','0345')

SQL语句：
select a.JID,isnull(b.caseno,0) from
(select distinct JID from OnlineLog group by JID) a left join
(select JID,count(1) as caseno from CurrentTask group by JID) b
on a.JID=b.JID
结果集如下：
0328 0
0345 4
0416 3
1550 2

[drmy]

各位，因为我的这个sql要用在Infomix数据库中，用我自己那个sql出不来count为0的记录数，但是在SqlServer 2000中就完全正确。请问这是什么原因？各位给的答案我回去试试的

[westbulls]

会不会是infomix的分组与sql server不同,试试这个：
Select k.JID,isnull(kk.caseno,0) caseno
From (select jid from OnlineLog group by jid) k
left join (select jid,count(caseno) caseno from CurrentTask ) kk
On k.JID=kk.JID

[zousoft]

On OnlineLog.JID=CurrentTask.JID 没有0328当然也不会显示count(CurrentTask.caseno)。
我sql不好，帮你顶吧.

[drmy]

谢谢，我明天就试试

[PrgmLover]

同楼上mysearching2

[wyr521]

Select OnlineLog.JID,isnull(count(CurrentTask.caseno),0) From OnlineLog left join CurrentTask On OnlineLog.JID=CurrentTask.JID Group By OnlineLog.JID

[drmy]

能不能不用子查询，更快查询速度的方法有吗？

[mysearching2]

试试
Select OnlineLog.JID,isnull(count(CurrentTask.caseno),0) From OnlineLog left join CurrentTask On OnlineLog.JID=CurrentTask.JID Group By OnlineLog.JID