trySelect * From Bill A
Where Not Exists(Select 1 From SpecialDep Where cDep = A.cCus Or cDep = A.cDep)
Union All
Select * From Bill A
Where Exists(Select 1 From SpecialDep Where cDep = A.cCus And prop Is Null)
Where Not Exists(Select 1 From SpecialDep Where cDep = A.cCus Or cDep = A.cDep)
Union All
Select * From Bill A
Where Exists(Select 1 From SpecialDep Where cDep = A.cCus And prop Is Null)
Where Not Exists(Select * From SpecialDep Where cDep = a.cCus and cDep = a.cDep)
or
Exists(Select * From SpecialDep Where cDep = a.cCus And prop Is Null)
(cCus Nvarchar(10),
cDep Nvarchar(10))
Insert Bill Select N'分店1', N'部门1'
Union All Select N'分店2', N'部门2'
Union All Select N'分店3', N'部门3'
Union All Select N'分店4', N'部门4'Create Table SpecialDep
(cDep Nvarchar(10),
prop Nvarchar(10))
Insert SpecialDep Select N'部门3', 1
Union All Select N'分店3', 3
Union All Select N'分店2', null
GO
Select * From Bill A
Where Not Exists(Select 1 From SpecialDep Where cDep = A.cCus Or cDep = A.cDep)
Union
Select * From Bill A
Where Exists(Select 1 From SpecialDep Where cDep = A.cCus And prop Is Null)Select * From Bill A
Where Not Exists(Select 1 From SpecialDep Where cDep = A.cCus Or cDep = A.cDep)
Or Exists(Select 1 From SpecialDep Where cDep = A.cCus And prop Is Null)
GO
Drop Table Bill,SpecialDep
--Result
/*
cCus cDep
分店1 部门1
分店2 部门2
分店4 部门4
*/