如下SQL应该怎么写?

 

2014-9-27 15:54 上传

下载附件 (18.13 KB)

建表:
create table t_result
(d date,result varchar2(4));

插入数据:
insert into t_result values ('2014-01-01','胜');
insert into t_result values ('2014-01-01','胜');
insert into t_result values ('2014-01-01','负');
insert into t_result values ('2014-01-02','胜');
insert into t_result values ('2014-01-02','负');
insert into t_result values ('2014-01-02','负');


我写法如下:
select t1.d,t1.c1 '胜',t2.c2 '负' from
(select count(result) c1,d from t_result  where result = '胜' group by d) t1
LEFT outer join
(select count(result) c2,d from t_result  where result = '负' group by d) t2
on t1.d = t2.d


但这样就要扫描两次表，还有其他更优化的写法么？