--建一个临时表变量并插入测试数据
Declare @tmpTable table (dbName nvarchar(30),status varchar(1))insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test1','y')
insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test2','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test4','y')
insert into @tmpTable (dbName,status) values('test5','y')
insert into @tmpTable (dbName,status) values('test5','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')--查询结果:取出不重复的、以dbName值为组、并且status全部为'n'的 dbName 值
select dbName from (
select * from @tmpTable
group by dbname,status
) b
where dbName not in(
select dbName from (
select * from @tmpTable
group by dbname,status
) a where status = 'y'
)
---------------------------------------
上面的查询语句能够得到正确的结果:
test2
test6----------------------
现在的问题是,该语句进行了3次查询,如何优化此查询语句?
Declare @tmpTable table (dbName nvarchar(30),status varchar(1))insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test1','y')
insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test2','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test4','y')
insert into @tmpTable (dbName,status) values('test5','y')
insert into @tmpTable (dbName,status) values('test5','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')--查询结果:取出不重复的、以dbName值为组、并且status全部为'n'的 dbName 值
select dbName from (
select * from @tmpTable
group by dbname,status
) b
where dbName not in(
select dbName from (
select * from @tmpTable
group by dbname,status
) a where status = 'y'
)
---------------------------------------
上面的查询语句能够得到正确的结果:
test2
test6----------------------
现在的问题是,该语句进行了3次查询,如何优化此查询语句?
insert into @tmpTable (dbName,status) values('test1','y')
insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test2','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test4','y')
insert into @tmpTable (dbName,status) values('test5','y')
insert into @tmpTable (dbName,status) values('test5','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n') select distinct dbname from @tmpTable a
where not exists(select 1 from @tmpTable where a.dbname=dbname and status='y')
/*dbname
------------------------------
test2
test6(所影响的行数为 2 行)*/
insert into @tmpTable (dbName,status) values('test1','y')
insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test2','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test4','y')
insert into @tmpTable (dbName,status) values('test5','y')
insert into @tmpTable (dbName,status) values('test5','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n') select distinct dbName from @tmpTable a where not exists(select 1 from @tmpTable where dbName=a.dbName and status = 'y' )
not in 和 not exists哪个效率较高?
insert into @tmpTable (dbName,status) values('test1','y')
insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test2','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test4','y')
insert into @tmpTable (dbName,status) values('test5','y')
insert into @tmpTable (dbName,status) values('test5','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n') select distinct * from @tmpTable a
where exists(
select * from (select distinct * from @tmpTable) tp
where dbname=a.dbname group by dbname having count(1)=1)
and status='n'dbName status
test2 n
test6 n
---
05好像一个速度
他们都说not exists 好,所以我信了
select distinct dbName from @tmpTable a where dbName not in (select dbName from @tmpTable where status = 'y' )
insert into @tmpTable (dbName,status) values('test1','y')
insert into @tmpTable (dbName,status) values('test1','n')
insert into @tmpTable (dbName,status) values('test2','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test3','y')
insert into @tmpTable (dbName,status) values('test3','n')
insert into @tmpTable (dbName,status) values('test4','y')
insert into @tmpTable (dbName,status) values('test5','y')
insert into @tmpTable (dbName,status) values('test5','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n')
insert into @tmpTable (dbName,status) values('test6','n') select a.dbName from @tmpTable a left join @tmpTable b on a.dbName=b.dbName and a.status<>'y' and b.status='y'
where a.status<>'y' and b.dbName is null
group by a.dbName
where not exists(select 1 from @tmpTable where a.dbname=dbname and status='y')
group by dbname
where not exists(select 1 from @tmptable where dbname = a.dbname and status <> 'n')