日期:2014-05-17 浏览次数:20629 次
/**
我想查询表一(tb1)中KPBZ='y'的记录,并且
不在表二(tb2)中 或者在表二中但是BZ不为null的记录
*/
DECLARE @TB1 TABLE(DM VARCHAR(10),KPBZ VARCHAR(2))
INSERT INTO @TB1(DM,KPBZ)
SELECT '11-AA','y' UNION ALL
SELECT '22-AA','y' UNION ALL
SELECT '33-AA','y' UNION ALL
SELECT '44-AA','y' UNION ALL
SELECT '55-AA','n'
DECLARE @TB2 TABLE(DM VARCHAR(10),BZ VARCHAR(2))
INSERT INTO @TB2(DM,BZ)
SELECT '11-AA','JF' UNION ALL
SELECT '22-AA',NULL
--结果应该是:11-AA,33-AA,44-AA
--我用的查询是:
SELECT * FROM @TB1 a --WITH(NOLOCK)
WHERE a.KPBZ='y'
AND (NOT EXISTS(SELECT TOP(1)1 FROM @TB2 b WHERE a.DM=b.DM)
OR EXISTS(SELECT TOP(1)1 FROM @TB2 b WHERE a.DM=b.DM AND ISNULL(b.BZ,'')>'')
)
--谁有其他的写法?谢谢,最好效率比这个好。
DECLARE @TB1 TABLE(DM VARCHAR(10),KPBZ VARCHAR(2))
INSERT INTO @TB1(DM,KPBZ)
SELECT '11-AA','y' UNION ALL
SELECT '22-AA','y' UNION ALL
SELECT '33-AA','y' UNION ALL
SELECT '44-AA','y' UNION ALL
SELECT '55-AA','n'
DECLARE @TB2 TABLE(DM VARCHAR(10),BZ VARCHAR(2))
INSERT INTO @TB2(DM,BZ)
SELECT '11-AA','JF' UNION ALL
SELECT '22-AA',NULL
select * from @tb1 a left join
@tb2 b on a.dm=b.dm and b.bz is not null
where a.kpbz='y' and b.dm is null
/**
我想查询表一(tb1)中KPBZ='y'的记录,并且
不在表二(tb2)中 或者在表二中但是BZ不为null的记录
*/
create TABLE TB1(DM VARCHAR(10),KPBZ VARCHAR(2))
INSERT INTO TB1(DM,KPBZ)
SELECT '11-AA','y' UNION ALL
SELECT '22-AA','y' UNION ALL
SELECT '33-AA','y' UNION ALL
SELECT '44-AA','y' UNION ALL
SELECT '55-AA','n'
create TABLE TB2 (DM VARCHAR(10),BZ VARCHAR(2))
INSERT INTO TB2(DM,BZ)
SELECT '11-AA','JF' UNION ALL
SELECT '22-AA',NULL
set statistics time on
set statistics io on
--你的查询
SELECT * FROM TB1 a --WITH(NOLOCK)
WHERE a.KPBZ='y'
AND (NOT EXISTS(SELECT TOP(1)1 FROM TB2 b WHERE a.DM=b.DM)
OR EXISTS(SELECT TOP(1)1 FROM TB2 b WHERE a.DM