表 TAB_A 字段 id,name,type
表 TAB_B 字段 id,aId,status 其中aId关联TAB_A的id1、select a.id,a.name,a.type,b.id from TAB_A ,TAB_B b where a.type=2 and a.id=b.aId and b.status!=1
2、 select a.id,a.name,a.type,b.id from TAB_A a where a.type=2 and a.id not in(select b.aId from TAB_B b)现在要将这两条语句合并,就是将这两条语句查询出的数据用一条语句直接一起查询出来
表 TAB_B 字段 id,aId,status 其中aId关联TAB_A的id1、select a.id,a.name,a.type,b.id from TAB_A ,TAB_B b where a.type=2 and a.id=b.aId and b.status!=1
2、 select a.id,a.name,a.type,b.id from TAB_A a where a.type=2 and a.id not in(select b.aId from TAB_B b)现在要将这两条语句合并,就是将这两条语句查询出的数据用一条语句直接一起查询出来
union all
select a.id,a.name,a.type,b.id from TAB_A a where a.type=2 and a.id not in(select b.aId from TAB_B b)
select a.id,a.name,a.type,b.id from TAB_A ,TAB_B b where a.type=2 and a.id=b.aId and b.status!=1
union all
select a.id,a.name,a.type,b.id from TAB_A a where a.type=2 and a.id not in(select b.aId from TAB_B b)
,b.id 怎么出来的?
select a.id,a.name,a.type,b.id
from Tab_A a,Tab_B b
where a.type = 2
and 1 = case when b.status <> 1 then 1 else 0 end
case 不会用。。
我的实际SQL是
select a.id,a.name,a.org_id
from pi_t_personnel a,pi_t_apply_join_party b
where a.personnel_status=2 and a.org_id=101
and 1 = case when b.personnel_id=a.id and b.join_degree<>1 then 1 else 0 end
select a.id,a.name,a.org_id
from pi_t_personnel a,pi_t_apply_join_party b
where a.personnel_status=2 and a.org_id=101
and 1 = case when b.personnel_id=a.id and b.join_degree<>1 then 1 else 0 end
如果
select a.id,a.name,a.org_id
from pi_t_personnel a,pi_t_apply_join_party b
where a.personnel_status=2 and a.org_id=101
and 1 = case
when b.personnel_id=a.id and b.join_degree<>1 then 1
when a.id not in(select b.aId from TAB_B b then 1
else 0 end
又会出现数据重复