无法验证SQL查询的计数

Question

我试图验证表的计数，但查询持续运行了20分钟。可能出了什么问题？

IF((select COUNT(1) from test.[dbo].[as_EmployeeData] (nolock) ed
JOIN test.[dbo].DepartmentData dd ON ed.EmployeeId= dd.DepartmentData
AND dd.Name = 'IT' AND dd.Status = 'Completed')= 0
)
BEGIN
PRINT 'Successful'
END
ELSE
BEGIN
PRINT 'Failed'
END

而如果我运行下面的查询，我得到的结果是0

select COUNT(1) from test.[dbo].[as_EmployeeData] (nolock) ed
JOIN test.[dbo].DepartmentData dd ON ed.EmployeeId= dd.DepartmentData
AND dd.Name = 'IT' AND dd.Status= 'Completed'

Answer 1

一种选择是将其粘贴在变量中：

DECLARE @myCount INT = (SELECT COUNT(1) 
FROM test.[dbo].[as_EmployeeData] ed
INNER JOIN test.[dbo].DepartmentData dd 
ON dd.DepartmentData = ed.EmployeeId 
AND dd.Name = 'IT' 
AND dd.Status= 'Completed');
IF @myCount != 0
BEGIN 
PRINT 'Successful'
END
ELSE
BEGIN
PRINT 'Failed'
END

然而，随着越来越多的数据被输入，COUNT将逐渐变得越来越低效。

您最好的选择是进行EXISTS检查：

IF EXISTS (SELECT TOP 1 1 
FROM test.[dbo].[as_EmployeeData] ed
INNER JOIN test.[dbo].DepartmentData dd 
ON dd.DepartmentData = ed.EmployeeId 
AND dd.Name = 'IT' 
AND dd.Status= 'Completed')
BEGIN
PRINT 'Successful'
END
ELSE 
BEGIN
PRINT 'Failed'
END

话虽如此，但我看不出有什么合理的理由提出这样的质疑；你还在if/else区块做什么？

Answer 2

您可以尝试执行top 1而不是计数，然后使用现有的

IF ( exists( select top 1 1
from test.[dbo].[as_EmployeeData] ed
JOIN test.[dbo].DepartmentData dd 
ON ed.EmployeeId = dd.DepartmentData
AND dd.Name = 'IT' 
AND dd.Status = 'Completed'
)
)
BEGIN
PRINT 'Successful'
END
ELSE
BEGIN
PRINT 'Failed'
END

Answer 3

IF NOT EXISTS(SELECT 1 
FROM test.[dbo].[as_EmployeeData] AS ED WITH(NOLOCK)
INNER JOIN test.[dbo].DepartmentData AS DD WITH(NOLOCK) ON ED.EmployeeId = DD.DepartmentData
WHERE DD.[Name] = 'IT' AND DD.[Status] = 'Completed')
BEGIN
PRINT 'Successful'
END
ELSE
BEGIN
PRINT 'Failed'
END