求两次查询合并到一个SQL语句

[boxnoodle]

第一次查询语句为

select a.STOREID,a.fj,a.cbdw,a.xbrq,a.dbsx,a.dbbh,a.dbrq,a.dblb,a.lwdw,b.blqkwj,b.blrq,b.blqk,c.bljgwj
from tab801 a,tab805 b,tab808 c
where a.cbbljg_doc = concat('808.',c.storeid) and a.cbblqk_doc = concat('805.',b.storeid)
order by a.storeid

对查询的结果，进行第二次查询，查询条件是

where EXISTS(SELECT 1 FROM tab801_$$$_TEMP t WHERE tab801.STOREID=t.ID AND t.FLAG=1)

也即对第一次查询的结果，再根据表tab801的主键，过滤出只在tab801_$$$_TEMP中存在该主键的记录。

[BlueskyWide]

--直接放在一起不是也可以吗?

select a.STOREID,
       a.fj,
       a.cbdw,
       a.xbrq,
       a.dbsx,
       a.dbbh,
       a.dbrq,
       a.dblb,
       a.lwdw,
       b.blqkwj,
       b.blrq,
       b.blqk,
       c.bljgwj
   from tab801 a,
        tab805 b,
        tab808 c,
        tab801_$$$_TEMP t
   where a.cbbljg_doc = concat('808.',c.storeid) and 
         a.cbblqk_doc = concat('805.',b.storeid) and 
         a.STOREID=t.ID 
         AND t.FLAG=1
   order by a.storeid;

[huangdh12]

直接连接不行吗？

SELECT * FROM (select a.STOREID,a.fj,a.cbdw,a.xbrq,a.dbsx,a.dbbh,a.dbrq,a.dblb,a.lwdw,b.blqkwj,b.blrq,b.blqk,c.bljgwj
from tab801 a,tab805 b,tab808 c
where a.cbbljg_doc = concat('808.',c.storeid) and a.cbblqk_doc = concat('805.',b.storeid)
order by A.STOREID) A WHERE EXISTS(SELECT 1 FROM tab801_$$$_TEMP t WHERE A.STOREID=t.ID AND t.FLAG=1);
不知道能否符合你的要求

[szflower]

select * from
(select a.STOREID,a.fj,a.cbdw,a.xbrq,a.dbsx,a.dbbh,a.dbrq,a.dblb,a.lwdw,b.blqkwj,b.blrq,b.blqk,c.bljgwj
from tab801 a,tab805 b,tab808 c
where a.cbbljg_doc = concat('808.',c.storeid) and a.cbblqk_doc = concat('805.',b.storeid)
order by a.storeid
) aa
where EXISTS(SELECT 1 FROM tab801_$$$_TEMP t WHERE aa.STOREID=t.ID AND t.FLAG=1)

[butchroller]

select a.STOREID,a.fj,a.cbdw,a.xbrq,a.dbsx,a.dbbh,a.dbrq,a.dblb,a.lwdw,b.blqkwj,b.blrq,b.blqk,c.bljgwj 
from tab801 a,tab805 b,tab808 c 
where a.cbbljg_doc = concat('808.',c.storeid) and a.cbblqk_doc = concat('805.',b.storeid) 
    and EXISTS(SELECT 1 FROM tab801_$$$_TEMP t WHERE a.STOREID=t.ID AND t.FLAG=1)
order by a.storeid

[xcluii]

select mytable.*
（select a.STOREID,a.fj,a.cbdw,a.xbrq,a.dbsx,a.dbbh,a.dbrq,a.dblb,a.lwdw,b.blqkwj,b.blrq,b.blqk,c.bljgwj
from tab801 a,tab805 b,tab808 c
where a.cbbljg_doc = concat('808.',c.storeid) and a.cbblqk_doc = concat('805.',b.storeid)
order by a.storeid） mytable
where EXISTS
(SELECT 1 FROM tab801_$$$_TEMP t, tab801, mytable 
WHERE tab801.storeid = t.ID and t.flag = 1 and tab801.storeid = mytable.storeid
)

不知是否可以

[codearts]

select * from
(select a.STOREID,a.fj,a.cbdw,a.xbrq,a.dbsx,a.dbbh,a.dbrq,a.dblb,a.lwdw,b.blqkwj,b.blrq,b.blqk,c.bljgwj
from tab801 a,tab805 b,tab808 c
where a.cbbljg_doc = concat('808.',c.storeid) and a.cbblqk_doc = concat('805.',b.storeid)
and rownum >= 1 --加个rownum,执行计划就会不一样，效率也会不同
order by a.storeid
) aa
where EXISTS(SELECT 1 FROM tab801_$$$_TEMP t WHERE aa.STOREID=t.ID AND t.FLAG=1)