日期:2014-05-17 浏览次数:20727 次
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
GO
create table [A]([aid] int,[a1] varchar(2),[a2] varchar(2),[a3] int)
insert [A]
select 1,'t1','f2',1 union all
select 2,'t2','f2',1 union all
select 3,'t3','f3',2
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
GO
create table [B]([bid] int,[aid] varchar(1),[b1] varchar(1),[b2] varchar(1),[b3] varchar(1))
SELECT A.aid, a1, a2, b1, b2 FROM A LEFT OUTER JOIN B ON
a.aid = b.aid WHERE a3 = 1 AND (b3 = 1 OR b3 is null)
/*
aid a1 a2 b1 b2
----------- ---- ---- ---- ----
1 t1 f2 NULL NULL
2 t2 f2 NULL NULL
(2 行受影响)
*/
drop table [A]
drop table [B]
if OBJECT_ID('TableA','u') is not null
drop table TableA
create table TableA
(
aid int primary key,
a1 nvarchar(5),
a2 nvarchar(5),
a3 int
)
go
insert into TableA
select 1, 't1', 'f2', 1 union all
select 2, 't2', 'f2', 1 union all
select 3, 't3', 'f3', 2
if OBJECT_ID('TableB','u') is not null
drop table TableB
create table TableB
(
bid int primary key,
aid int,
b1 nvarchar(5),
b2 nvarchar(5),
b3 int
)
go
--增加一条B
--从而使结果中一条有B数据,一条没有B数据
--方便对比查看
insert into TableB
select 1,1,'ss','dd',1
--SQL
select A.aid,a1,a2,b1,b2 from TableA A
left join TableB B on A.aid=b.aid
where A.a3=1 and ((b3=1 and b3 is not null) or b3 is null)
--TableA
--1 t1 f2 1
--2 t2 f2 1
--3 t3 f3 2
--TableB
--1 1 ss dd 1
--结果集
--1 t1 f2 ss dd
--2 t2 f2 NULL NULL