对表进行分组统计的实现,哪为高手请指教(最好能够用SQL文解决),高分赠送!

Question

第6,7个字段为一个Flag标记，两个字段只有一个标记吗？我只按一个写吧：
select field1,field2,field3,field4,field5,
   sum(decode(field6,'1',1,'0',0)) as sum_readed,
   sum(decode(field6,'1',0,'0',1)) as sum_unreaded 
from mytable
group by field1,field2,field3,field4,field5;

Answer 1

select field1,field2,field3,field4,field5,
   sum(field6) sum_readed,
   (count(field6) - sum(field6)) sum_unreaded 
from mytable
group by field1,field2,field3,field4,field5;

Answer 2

不知你所为的两个字段来进行统计，以下同时检索：select field1,field2,field3,field4,field5,
   sum(decode(field6,'1',1,0) or decode(field7,'1',1,0)) readed,
   (count(*)-(sum(decode(field6,'1',1,0) or decode(field7,'1',1,0)))) unreaded 
from mytable
group by field1,field2,field3,field4,field5;

Answer 3

select field1,field2,field3,field4,field5,
   sum(to_number(bitand(decode(field6,'1',1,0),decode(field7,'1',1,0)))) readed,
   (count(*)-(sum(to_number(bitand(decode(field6,'1',1,0),decode(field7,'1',1,0))))) unreaded 
from mytable
group by field1,field2,field3,field4,field5;