求高效率统计方法

[xiaoxi99cspy]

表结构如下：
mailId(邮件id) userId(用户id) maillocation(邮件位置,有收件箱inbox,发件箱sendbox,垃圾箱trashbox三种) mailsize(邮件大小)
现在要分别统计每个用户的inbox,sendbox,trashbox三个邮箱的邮件数和大小，怎么写效率高啊

我想扫描一变纪录统计出，用case语句,但不知为何不对
select count(case when userindex='ca8USERID00000002943f262f0600000041' and maillocation='inbox' then 1 else 0 end) count1,
count(case when userindex='ca8USERID00000002943f262f0600000041' and maillocation='sendbox' then 1 else 0 end) count2,
count(case when userindex='ca8USERID00000002943f262f0600000041' and maillocation='trashbox' then 1 else 0 end) count3,
count(case when userindex='caUSERID00000003333f262f0600000036' and maillocation='inbox' then 1 else 0 end) count4
count(case when userindex='caUSERID00000003333f262f0600000036' and maillocation='sendbox' then 1 else 0 end) count5
count(case when userindex='caUSERID00000003333f262f0600000036' and maillocation='trashbox' then 1 else 0 end) count6
from mailinfo ;

得到的结果是总的纪录个数,并不是单个的纪录,我用的oracle9i


还有邮件总的大小,各位大虾,能不能给出效率高点的方法

[pengdali]

select sum(case when userindex='ca8USERID00000002943f262f0600000041' and maillocation='inbox' then 1 else 0 end) count1,
sum(case when userindex='ca8USERID00000002943f262f0600000041' and maillocation='sendbox' then 1 else 0 end) count2,
sum(case when userindex='ca8USERID00000002943f262f0600000041' and maillocation='trashbox' then 1 else 0 end) count3,
sum(case when userindex='caUSERID00000003333f262f0600000036' and maillocation='inbox' then 1 else 0 end) count4
sum(case when userindex='caUSERID00000003333f262f0600000036' and maillocation='sendbox' then 1 else 0 end) count5
sum(case when userindex='caUSERID00000003333f262f0600000036' and maillocation='trashbox' then 1 else 0 end) count6
from mailinfo ;

[pengdali]

你这样写不行吗？

select userindex 用户编号,
sum(case when maillocation='inbox' then 1 else 0 end) 收件箱,
sum(case when maillocation='sendbox' then 1 else 0 end) 发件箱,
sum(case when maillocation='trashbox' then 1 else 0 end) 垃圾箱
from mailinfo group by userindex ;

[dajian2000]

为什么要把userindex写进去？想靠它提高检索速度吗？
用楼上的方法就好了，它同样利用了索引userindex 。

[xiaoxi99cspy]

大哥，我知道我错哪了，就把 else 0 改位else null就ok了