求高效率统计方法
表结构如下:
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
还有邮件总的大小,各位大虾,能不能给出效率高点的方法