select * from (
select c.bno,null as ccNull1,c.opcode,c.bop,c.bartime, c.zno from (
select a.bno,a.opcode,b.opcode as bop,a.bartime,a.zno from
(select * from tbilltrace where opcode=30 ) a ,
(select * from tbilltrace ) b where a.bno=b.bno and b.opcode!=30 and b.opcode!=50) c
union
select null as opNull2,d.bno,d.opcode,d.bop,d.bartime ,d.zno from (
select a.bno,a.opcode,bb.opcode as bop ,a.bartime,a.zno from
(select * from tbilltrace where opcode=50) a ,
(select * from tbilltrace ) bb where a.bno=bb.bno and bb.opcode!=30 and bb.opcode!=50)
d ) where to_char(bartime,'yyyy-mm-dd')='2007-09-27' and zno=075507
select c.bno,null as ccNull1,c.opcode,c.bop,c.bartime, c.zno from (
select a.bno,a.opcode,b.opcode as bop,a.bartime,a.zno from
(select * from tbilltrace where opcode=30 ) a ,
(select * from tbilltrace ) b where a.bno=b.bno and b.opcode!=30 and b.opcode!=50) c
union
select null as opNull2,d.bno,d.opcode,d.bop,d.bartime ,d.zno from (
select a.bno,a.opcode,bb.opcode as bop ,a.bartime,a.zno from
(select * from tbilltrace where opcode=50) a ,
(select * from tbilltrace ) bb where a.bno=bb.bno and bb.opcode!=30 and bb.opcode!=50)
d ) where to_char(bartime,'yyyy-mm-dd')='2007-09-27' and zno=075507
各位大哥帮帮忙,,,,谢谢谢谢 看看那里能优化,现在查好慢
1.建索引,这个不用多说了;
2.where子句的解析顺序是从右到左的,你可以把索引列的条件放在后面;
3.尽量不使用*;
4.表尽量使用别名;
5.尽量减少访问表的次数,你这个语句多次访问同一个表,感觉从逻辑上可以优化的,但需要你把表结构和希望查询的结果贴出来;
6.可以的话,使用union all
...
先改到如下吧,还有很大的余地,比如:用decode把union给合并了;
不得不说,代码写的太烂了。
SELECT C.BNO, NULL AS CCNULL1, C.OPCODE, C.BOP, C.BARTIME, C.ZNO
FROM (SELECT A.BNO, A.OPCODE, B.OPCODE AS BOP, A.BARTIME, A.ZNO
FROM (SELECT *
FROM TBILLTRACE
WHERE OPCODE = 30
AND ZNO = 075507
AND TO_CHAR(BARTIME, 'yyyy-mm-dd ') = '2007-09-27') A,
(SELECT * FROM TBILLTRACE WHERE OPCODE NOT IN (30, 50)) B
WHERE A.BNO = B.BNO) C
UNION
SELECT NULL AS OPNULL2, D.BNO, D.OPCODE, D.BOP, D.BARTIME, D.ZNO
FROM (SELECT A.BNO, A.OPCODE, BB.OPCODE AS BOP, A.BARTIME, A.ZNO
FROM (SELECT *
FROM TBILLTRACE
WHERE OPCODE = 50
AND ZNO = 075507
AND TO_CHAR(BARTIME, 'yyyy-mm-dd ') = '2007-09-27') A,
(SELECT * FROM TBILLTRACE OPCODE NOT IN (30, 50)) BB
WHERE A.BNO = BB.BNO) D