select * from table t where t.depart_nm='销售部' and t.status='售出' and t.sale_id not in (select sale_id from table t where t.depart_nm<>'销售部' and t.status='售出')
先把要查询主要的条件(比较容易区分的),然后再去除次要条件 SELECT * FROM t_order t WHERE t.dept = '销售部' AND t.status = '售出' AND t.orderid NOT IN (SELECT orderid FROM t_order t WHERE t.dept <> '销售部' AND t.status <> '售出');
--> 测试数据:a if object_id('a') is not null drop table a go create table a([销售单号]int,[负责部门] varchar(10),[状态] varchar(10)) insert a select 1 , '车间' , '生产中' union all select 1 , '库房' , '待售' union all select 1 , '销售部' , '售出' union all select 2 , '销售部' , '售出' union all select 3 , '车间' , '生产中' --查询语句 select * from a where a.[负责部门] ='销售部' and a.[状态]='售出' and a.[销售单号] not in ( select [销售单号] from a where a.[负责部门] <>'销售部' ) 销售单号 负责部门 状态 ----------- ---------- ---------- 2 销售部 售出(1 行受影响)
SELECT *
FROM t_order t
WHERE t.dept = '销售部'
AND t.status = '售出'
AND t.orderid NOT IN (SELECT orderid
FROM t_order t
WHERE t.dept <> '销售部'
AND t.status <> '售出');
if object_id('a') is not null drop table a
go
create table a([销售单号]int,[负责部门] varchar(10),[状态] varchar(10))
insert a
select
1 , '车间' , '生产中' union all
select 1 , '库房' , '待售' union all
select 1 , '销售部' , '售出' union all
select 2 , '销售部' , '售出' union all
select 3 , '车间' , '生产中'
--查询语句
select * from a
where a.[负责部门] ='销售部'
and a.[状态]='售出'
and a.[销售单号] not in
(
select [销售单号] from a where a.[负责部门] <>'销售部'
)
销售单号 负责部门 状态
----------- ---------- ----------
2 销售部 售出(1 行受影响)