code是jk(借款) state=6 可以展示，jk和code是cz(冲账)是关联关系，连接是ID和RelateDillID,现在需求是查询出没有冲账的借款单或者已经冲账的但是冲账单的state是(1,4,11) ,BillType等于1是借款单,2是冲账单
select * from
(select * from 表 a where a.BillType=1) a
left join (select * from 表 b where b.BillType=2) b on a.ID=b.RelateDillID
where b.id is null or b.state in (1,4,11)
SELECT A.* FROM 表 A WHERE NOT EXISTS(SELECT * FROM 表 B WHERE A.ID<>B.RELATEBILLID AND B.BILLTYPE='2') AND A.BILLTYPE='1'---没有冲账的
UNION ALL
SELECT A.* FROM 表 A WHERE EXISTS(SELECT * FROM 表 B WHERE A.ID=B.RELATEBILLID AND B.BILLTYPE='2') AND A.BILLTYPE='1' AND A.STATE IN ('1','4','11')
1min目标场景问卷
立即参与
