sql-server – 在仅使用文字值的WHERE子句中替换ISNULL()的不同

Table 'Users'. Scan count 20,logical reads 17564,lob read-ahead reads 0.
Table 'Workfile'. Scan count 0,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4844 ms,elapsed time = 863 ms.

重写交错合用于派生毗连会发生完全沟通的统统.我不规划从头宣布查询打算和统计信息 – 它们确实没有改变.

SELECT COUNT(u.Id)
FROM dbo.Users AS u
JOIN 
(
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x ON x.Id = u.Id;

相关代数：
为了彻底,并让Joe Celko不要困扰我的空想,我们至少必要实行一些稀疏的相关.这里没什么’！

实行行使INTERSECT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   INTERSECT
                   SELECT u.Age WHERE u.Age IS NOT NULL );

Table 'Users'. Scan count 1,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,elapsed time = 1090 ms.

这是除了以外的实行

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   EXCEPT
                   SELECT u.Age WHERE u.Age IS NULL);

Table 'Users'. Scan count 7,logical reads 9247,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2126 ms,elapsed time = 376 ms.

也许尚有其他要领来编写这些,但我会把这个留给那些也许比我更常行使EXCEPT和INTERSECT的人.

假如你真的必要一个计数
我在查询中行使COUNT作为一些简写(读：我偶然懒得想出更多涉及的场景).假如你只必要一个计数,你可以行使CASE表达式做同样的工作.

SELECT SUM(CASE WHEN u.Age < 18 THEN 1
                WHEN u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

SELECT SUM(CASE WHEN u.Age < 18 OR u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

它们都具有沟通的打算并具有沟通的CPU和读取特征.

Table 'Users'. Scan count 1,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 719 ms,elapsed time = 719 ms.

得胜者,冠军？
在我的测试中,在派生表上行使SUM的逼迫并行打算示意最佳.是的,很多这些查询都可以通过添加一些过滤索引来办理这两个谓词来帮助,但我想给其他人留下一些尝试.

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

感谢！

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!