SQL LEFT JOIN

[奔雷手]

INSERT INTO TESTA VALUES ('101','1001','10001');

--INSERT INTO TESTB VALUES ('201','2001','101');
--INSERT INTO TESTB VALUES ('201','2001','102');
--DELETE FROM TESTB;

A.A1对应B.A1
现在TESTA 的数据是固定的.
TESTB 的数据 如果存在A.A1=B.A1的情况下则返回TESTA 的这条数据
或者 如果TESTB 里无数据则 也返回TESTA 的这条数据
只有TESTB 里存在的数据A.A1<>B.A1 则不返回任何数据。
期望是一条SQL来解决

[發糞塗牆]

select a.* from testa a left join testb b on a.a1=b.a1 这样？

[IEEE_China]

 
if object_id('Tempdb..#TESTA') is not null drop table #TESTA	
if object_id('Tempdb..#TESTB') is not null drop table #TESTB	
create table #TESTA(
	[A1] varchar(10),
	[A2] varchar(10),
	[A3] varchar(10)
)
create table #TESTB(
	[B1] varchar(10),
	[B2] varchar(10),
	[A1] varchar(10)
)

INSERT INTO #TESTA VALUES ('101','1001','10001');
INSERT INTO #TESTA VALUES ('102','1001','10001');
INSERT INTO #TESTA VALUES ('103','1001','10001');
INSERT INTO #TESTA VALUES ('104','1001','10001');

INSERT INTO #TESTB VALUES ('201','2001','101');
INSERT INTO #TESTB VALUES ('201','2001','105');


select a.* from #TESTA a left join #TESTB b on a.A1=b.A1
--------------
A1         A2         A3
---------- ---------- ----------
101        1001       10001
102        1001       10001
103        1001       10001
104        1001       10001

(4 行受影响)

[奔雷手]

create table testA (A1 VARCHAR(10),A2 VARCHAR(10),A3 VARCHAR(10))
create table testB (B1 VARCHAR(10),B2 VARCHAR(10),A1 VARCHAR(10))

[rmljoe]

select a.*,b.*
from TESTA a
left join TESTB b
on a.A1 = b.A1
where 
exists(select 1 from TESTB)

[奔雷手]

引用 2 楼 Imaor 的回复:

 
if object_id('Tempdb..#TESTA') is not null drop table #TESTA	
if object_id('Tempdb..#TESTB') is not null drop table #TESTB	
create table #TESTA(
	[A1] varchar(10),
	[A2] varchar(10),
	[A3] varchar(10)
)
create table #TESTB(
	[B1] varchar(10),
	[B2] varchar(10),
	[A1] varchar(10)
)

INSERT INTO #TESTA VALUES ('101','1001','10001');
INSERT INTO #TESTA VALUES ('102','1001','10001');
INSERT INTO #TESTA VALUES ('103','1001','10001');
INSERT INTO #TESTA VALUES ('104','1001','10001');

INSERT INTO #TESTB VALUES ('201','2001','101');
INSERT INTO #TESTB VALUES ('201','2001','105');


select a.* from #TESTA a left join #TESTB b on a.A1=b.A1
--------------
A1         A2         A3
---------- ---------- ----------
101        1001       10001
102        1001       10001
103        1001       10001
104        1001       10001

(4 行受影响)

不行的。这样就算 插入一条 INSERT INTO #TESTB VALUES ('201','2001','105'); 结果还是有的。 我需要的是 这样的情况是没有数据的

[奔雷手]

引用 4 楼 rmljoe 的回复:

select a.*,b.*
from TESTA a
left join TESTB b
on a.A1 = b.A1
where 
exists(select 1 from TESTB)

不行的

[發糞塗牆]

一旦有105的整个结果都为空？

[奔雷手]

要达到的要求是 TESTB里 无数据 或者 有一条 且对应TESTA.A1=TESTB.A1 则 返回一条数据 否则 有一条 却 TESTA.A1<>TESTB.A1 则 没有数据返回

[奔雷手]

要达到的要求是 TESTB里 无数据 或者 只要其中有一条 且对应TESTA.A1=TESTB.A1 则 返回数据 否则 只有一条 却 TESTA.A1<>TESTB.A1 则 没有数据返回

[奔雷手]

引用 7 楼 DBA_Huangzj 的回复:

一旦有105的整个结果都为空？

是的，如果只有一条是105 则 为空

[發糞塗牆]

好像有点复杂

select * from #TESTA a 
WHERE 1=CASE WHEN 
(SELECT COUNT(1) FROM #TESTA a WHERE EXISTS (SELECT 1 FROM #TESTB b WHERE a.A1<>b.A1))+(SELECT COUNT(1) FROM #TESTA a WHERE EXISTS (SELECT 1 FROM #TESTB b WHERE a.A1=b.A1))<>(SELECT COUNT(1) FROM #TESTA) 
THEN 2 ELSE 1 END

[奔雷手]

引用 11 楼 DBA_Huangzj 的回复:

好像有点复杂

select * from #TESTA a 
WHERE 1=CASE WHEN 
(SELECT COUNT(1) FROM #TESTA a WHERE EXISTS (SELECT 1 FROM #TESTB b WHERE a.A1<>b.A1))+(SELECT COUNT(1) FROM #TESTA a WHERE EXISTS (SELECT 1 FROM #TESTB b WHERE a.A1=b.A1))<>(SELECT COUNT(1) FROM #TESTA) 
THEN 2 ELSE 1 END

虽然没看明白,但是结果不对,TESTB里没数据 结果也没数据。

[IEEE_China]

 
if object_id('Tempdb..#TESTA') is not null drop table #TESTA	
if object_id('Tempdb..#TESTB') is not null drop table #TESTB	
create table #TESTA(
	[A1] varchar(10),
	[A2] varchar(10),
	[A3] varchar(10)
)
create table #TESTB(
	[B1] varchar(10),
	[B2] varchar(10),
	[A1] varchar(10)
)

INSERT INTO #TESTA VALUES ('101','1001','10001');
INSERT INTO #TESTA VALUES ('102','1001','10001');
INSERT INTO #TESTA VALUES ('103','1001','10001');
INSERT INTO #TESTA VALUES ('104','1001','10001');

INSERT INTO #TESTB VALUES ('201','2001','101');
INSERT INTO #TESTB VALUES ('201','2001','105');
 
 
 select a.* from #TestA a left join #TestB b on a.A1=b.A1
where  (select  count(1) from #TestB where A1 not in(select A1 from #TestA))=0
------------
A1         A2         A3
---------- ---------- ----------

(0 行受影响)

[發糞塗牆]

select * from #TESTA a 
WHERE 1=CASE WHEN (
(SELECT COUNT(1) FROM #TESTA a WHERE EXISTS (SELECT 1 FROM #TESTB b WHERE a.A1<>b.A1))+(SELECT COUNT(1) FROM #TESTA a WHERE EXISTS (SELECT 1 FROM #TESTB b WHERE a.A1=b.A1))<>(SELECT COUNT(1) FROM #TESTA) )
AND  (SELECT COUNT(1) FROM #TESTB )<>0
THEN 2 ELSE 1 END

些少了一个条件