今天做在写SQL时候发现有需要优化的SQL 语句,特拿出来和大家 分享一下:
SELECT SceneryId
FROM TableBcst bcst WITH ( NOLOCK )
WHERE bcst.PayMode = 1
AND SceneryId NOT IN (
SELECT SceneryId
FROM TableBcst bcst WITH ( NOLOCK )
WHERE bcst.PayMode <> 1 )
查询二
SELECT DISTINCT
bcst1.SceneryId
FROM TableBcst bcst1 WITH ( NOLOCK )
WHERE bcst1.PayMode = 1
AND NOT EXISTS ( SELECT SceneryId
FROM [TableBcst bcst2 WITH ( NOLOCK )
WHERE bcst2.SceneryId = bcst1.SceneryId
AND ( bcst2.PayMode > 1
OR bcst2.PayMode < 1
) )
查询 三
SELECT DISTINCT
bcst1.SceneryId
FROM TableBcst bcst1 WITH ( NOLOCK )
WHERE bcst1.PayMode = 1
AND bcst1.SceneryId NOT IN (
SELECT SceneryId
FROM TableBcst bcst2 WITH ( NOLOCK )
WHERE bcst2.SceneryId = bcst1.SceneryId
AND ( bcst2.PayMode > 1
OR bcst2.PayMode < 1
))
以上三条语句查询结果是相同的,让我们在看看查询数据库统计结果如下:
前提条件是:如查询一,外查询的数据结果集只有子查询数据结果集的1%
SET STATISTICS TIME ON
统计结果如下:由于本人数据没有权限清除缓存区,所以部分数据不准确。
查询一
-------------------------------------------------------------------------------------------------
SQL Server 分析和编译时间:
查询二
---------------------------------------------------------------------------------------------
SQL Server 分析和编译时间:
查询三
-------------------------------------------------------------------------------------------------------------------
看看以上结果,也许有时候理论知识需要和实际情况相比较才能得出结论,在SQL Server 数据库技术内幕中理论也许你看过吧,有时候还是要看具体情况来判断吧。不要被统计结果给蒙骗了,因为数据量少,统计数据准确性不搞,今天下午刚好有空,抽时间和大家分享。