union之前是2594条,union之后是2179条,用adoquery测试的,大家看看咋办?
select
a.matid,b.lineid,b.lineord,
b.supplyname ,c.deptname,'1' linetype
from
qd2_matlinemain a,
qd2_matlinedetail b,qd2linebaseview1 c
where a.matlineid=b.matlineid
and lower(a.ismain)='true'
and a.ispordorassem='制造'
and b.lineid=c.lineid
and a.matid not in
(select matid from qd2_matlinemain_temp
where lower(valid)='true'
and ispordorassem='制造')
/*union
select
f.matid,d.lineid,d.lineord,
d.supplyname ,e.deptname,'1_1' linetype
from
qd2_matlinemain_temp f,
qd2_matlinedetail_temp d,qd2linebaseview1 e
where f.matlineid=d.matlineid
and f.ispordorassem='制造'
and d.lineid=e.lineid
and lower(f.valid)='true' */
select
a.matid,b.lineid,b.lineord,
b.supplyname ,c.deptname,'1' linetype
from
qd2_matlinemain a,
qd2_matlinedetail b,qd2linebaseview1 c
where a.matlineid=b.matlineid
and lower(a.ismain)='true'
and a.ispordorassem='制造'
and b.lineid=c.lineid
and a.matid not in
(select matid from qd2_matlinemain_temp
where lower(valid)='true'
and ispordorassem='制造')
/*union
select
f.matid,d.lineid,d.lineord,
d.supplyname ,e.deptname,'1_1' linetype
from
qd2_matlinemain_temp f,
qd2_matlinedetail_temp d,qd2linebaseview1 e
where f.matlineid=d.matlineid
and f.ispordorassem='制造'
and d.lineid=e.lineid
and lower(f.valid)='true' */
要不想减少就用union all
UNION : All distinct rows selected by either query. UNION ALL : All rows selected by either query, including all duplicates. INTERSECT : All distinct rows selected by both queries. MINUS : All distinct rows selected by the first query but not the second.