我写了一条SQL语句如下:
select a.strcd as 字符,a.strnam as 名称,sum(b.transamt) as 金额 from table a left outer join table b
on a.strcd=b.strcd and b.strdate>=to_date('2009-1-1','yyyy-mm-dd') group by a.strcd,a.strnam order by sum(b.transamt) desc
很快就有输出,但是我加了一个搜索条件后,就没有响应,好像死机一样,不知道错在哪了,请高人指点一下吧
select a.strcd as 字符,a.strnam as 名称,sum(b.transamt) as 金额 from table a left outer join table b
on a.status='0' and a.strcd=b.strcd and b.strdate>=to_date('2009-1-1','yyyy-mm-dd')
group by a.strcd,a.strnam order by sum(b.transamt) desc
select a.strcd as 字符,a.strnam as 名称,sum(b.transamt) as 金额 from table a left outer join table b
on a.strcd=b.strcd and b.strdate>=to_date('2009-1-1','yyyy-mm-dd') group by a.strcd,a.strnam order by sum(b.transamt) desc
很快就有输出,但是我加了一个搜索条件后,就没有响应,好像死机一样,不知道错在哪了,请高人指点一下吧
select a.strcd as 字符,a.strnam as 名称,sum(b.transamt) as 金额 from table a left outer join table b
on a.status='0' and a.strcd=b.strcd and b.strdate>=to_date('2009-1-1','yyyy-mm-dd')
group by a.strcd,a.strnam order by sum(b.transamt) desc
on a.strcd=b.strcd and a.status='0' and b.strdate>=to_date('2009-1-1','yyyy-mm-dd')
group by a.strcd,a.strnam order by sum(b.transamt) desc
select a.strcd as 字符, a.strnam as 名称, sum(b.transamt) as 金额
from (select a1.strcd ,a1.strnam * from table1 a1 where a.status='0') a
left outer join
(select b1.transamt,b1.strdate,b1.strcd from table2 where b.strdate > to_date('2008-12-31', 'yyyy-mm-dd')) b
on a.strcd = b.strcd
group by a.strcd, a.strnam
order by sum(b.transamt) desc改了几个地方:
1,>=变成了>
2,条件分别放入表a表b 这样就left outer join 的时候就少了连接。
还有你可以将 status 字段加索引试试!
on a.strcd=b.strcd and b.strdate>=to_date('2009-1-1','yyyy-mm-dd')
where a.status='0' and and b.transamt is null
group by a.strcd,a.strnam order by sum(b.transamt) desc
过滤a表记录放在where后面,哪有放在左连接后面的?如果过滤b表可以放在左连接后面!
这句语法上的问题,再基础不过了!基础不过关!