select distinct a.recordid, a.cwyear,a.cwmonth from bm_setmonth a left join bm_setspbm b on a.recordid=b.recordid where b.excutesalebz='F' or b.recordid is null
select a.* from bm_setmonth a where not exists(select 1 from bm_setspbm where excutesalebz!='F' and recordid=a.recordid)
谢谢各位,后来我自己又找到了一种方法: select distinct recordid,cwyear,cwmonth from bm_setmonth where recordid not in(select recordid from bm_setspbm where excutesalebz='T')
楼主自己的语句还可以简化一点。Select * from bm_setmonth Where recordid Not In(Select recordid from bm_setspbm Where excutesalebz='T')
a.*
from
bm_setmonth a
where
not exists(select 1 from bm_setspbm where excutesalebz!='F' and recordid=a.recordid)
select distinct recordid,cwyear,cwmonth from bm_setmonth
where recordid not in(select recordid from bm_setspbm where excutesalebz='T')
--建立测试环境Create table bm_setmonth
(recordid Int,
cwyear Int,
cwmonth varchar(20)
)Create table bm_setspbm
(recordid Int,
spbm varchar(20),
rkbz varchar(20),
salebz varchar(20),
excutesalebz varchar(20)
)
GO
--插入数据
Insert bm_setmonth Values(1, '2005', '02')
Insert bm_setmonth Values(20, '2005', '04')
Insert bm_setmonth Values(24, '2005', '08') Insert bm_setspbm Values(1, '01010059', 'T', 'F', 'T')
Insert bm_setspbm Values(1, '01010063', 'T', 'F', 'T')
Insert bm_setspbm Values(20, '01010067', 'F', 'T', 'F')
Insert bm_setspbm Values(20, '01010068', 'F', 'T', 'F')
GO
--测试Select * from bm_setmonth Where recordid Not In(Select recordid from bm_setspbm Where excutesalebz='T')--删除测试环境
Drop table bm_setmonth
Drop table bm_setspbm
--结果
/*
recordid cwyear cwmonth
20 2005 04
24 2005 08
*/