表名T1 ID 科目(VCODE) 借方(DR) 贷方(CR) INVOCE_NO 16800 1001 100 209
16800 1002 200 表名T2
16800 5001 100
16800 5001 200请问如何让查询 INVOCE_NO=209时,得到如下结果
ID 科目(VCODE) 借方(DR) 贷方(CR) INVOCE_NO16800 1001 100 209
16800 1002 200
16800 5001 100
16800 5001 200
16800 1002 200 表名T2
16800 5001 100
16800 5001 200请问如何让查询 INVOCE_NO=209时,得到如下结果
ID 科目(VCODE) 借方(DR) 贷方(CR) INVOCE_NO16800 1001 100 209
16800 1002 200
16800 5001 100
16800 5001 200
select ID,VCODE,DR,CR,INVOCE_NO
from t1
where t1.id in(select id from t1 where INVOCE_NO = 209)
union
select ID,VCODE,DR,CR,null
from t2
where t2.id in(select id from t1 where INVOCE_NO = 209)
;
select t1.id,t1.vcode,t.dr,t1.cr,t1.invoce_no
from t1
union
select t2.id,t2.vcode,t2.dr,t2.cr,t2.invoce_no
from t2
from t1,t2
where t1.id=t2.id
and t1.invoce_no=209
order by dr
from t1
where t1.id in(select id from t1 where INVOCE_NO = 209)
union
select ID,VCODE,DR,CR,null
from t2 where t2.id in(select id from t1 where INVOCE_NO = 209)这个可以