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来解决
------解决方案--------------------
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
些少了一个条件