- SQL code
select t2.esc_Orderid, t1.payment_id from payment t1 left join ctoc_order t2 on t2.order_id = t1.service_order where t1.payment_id in('820120528094530833273','820120528122552834900')--根据支付ID,显示订单ID和支付ID.这样查询会关联2个表,有没有速度快一点的方式.顺便求一个好用点的sql性能分析器.------解决方案--------------------
抢个沙发，呵呵
lz可以试试下面的语句：
select t2.esc_Orderid, t1.payment_id from
payment t1,ctoc_order t2
where t2.order_id = t1.service_order and t1.payment_id
between '820120528094530833273' and '820120528122552834900';
关于sql性能分析器，这个我也不是太清楚了！
------解决方案--------------------
一个优化技巧，楼主应该避免使用IN 或者是NOT IN 语句，在使用IN和not in时会导致全表扫描速度肯定就慢了瑟可以尝试一下修改看看能不能提升性能：
原语句：
select t2.esc_Orderid, t1.payment_id from
payment t1 left join ctoc_order t2
on t2.order_id = t1.service_order
where t1.payment_id
in(
'820120528094530833273',
'820120528122552834900')
更改后语句 先根据payment_id 查询t1表中的server_code然后在进行连接
select aa.payment_id,bb.esc_orderid from
(select payment_id,server_order from payment where payment_id='820120528094530833273'
union all
select payment_id,server_order from payment where payment_id='820120528122552834900') aa
left outer join ctoc_order bb on aa.server_order=bb.order_id;
------解决方案--------------------
in 或者not in的范围不大的时候还是会走索引的。优化器会自己转换的。
如果你那个in 语句能极大的缩小结果集那应该先做in查询然后再关联。比如100w条，你left join 完了，再一个in 要期中一万条，肯定不如先从100w条里取出1w条再管连快。当然优化器会做优化，但是你不能保证他一定会优化。
- SQL code
with aa as(select t1.payment_id,t1.service_orderfrom t1where t1.payment_id in ('820120528094530833273','820120528122552834900'))select t2.esc_orderid,aa.payment_idfrom aa left join t2 on t2.order_id = t1.service_order
------解决方案--------------------
- SQL code
create table s41071030( sno int , sname varchar2(10), ssex char(1), sage int, sdept varchar2(10));/create table c41071030( cno int, cname varchar2(10), cpno int, ccredit varchar2(10));/create table sc41071030( sno int, cno int, grade int );/insert into sc41071030select 1001,1,40 from dual union allselect 1001,2,45 from dual union allselect 1001,3,50 from dual union allselect 1002,1,44 from dual union allselect 1002,2,40 from dual union all--select 1002,3,50 from dual union allselect 1003,1,60 from dual;/insert into s41071030(sno,sdept)select 1001,'CS' from dual union allselect 1002,'CS' from dual union allselect 1003,'CS' from dual union allselect 1004,'dd' from dual;/with cte as( select a.sno ,count(1) as v_count from sc41071030 a inner join s41071030 b on a.sno=b.sno where b.sdept='CS' group by a.sno order by count(1) desc )select a.* from cte a inner join ( select max(v_count) as v_count from cte)b on a.v_count=b.v_count /*drop table s41071030;drop table c41071030;drop table sc41071030;*/