将选择计数（*）值保存为整数（SQL Server） [英] Saving a select count(*) value to an integer (SQL Server)

问题描述

我对此声明有些麻烦，因为毫无疑问我对从此选择声明返回的内容不了解：

I'm having some trouble with this statement, owing no doubt to my ignorance of what is returned from this select statement:

declare @myInt as INT
set @myInt = (select COUNT(*) from myTable as count)

if(@myInt <> 0) 
begin
   print 'there's something in the table'
end

myTable中有记录，但是当我运行上述代码时，print语句永远不会运行。进一步的检查表明，在上述赋值之后，myInt实际上为零。我确定我会丢失某些内容，但是我认为选择计数将返回一个可以在上面使用的标量？

There are records in myTable, but when I run the code above the print statement is never run. Further checks show that myInt is in fact zero after the assignment above. I'm sure I'm missing something, but I assumed that a select count would return a scalar that I could use above?

推荐答案

如果@myInt为零，则表示表中没有行：如果从不设置，则为NULL。

If @myInt is zero it means no rows in the table: it would be NULL if never set at all.

COUNT始终返回一行，即使没有

COUNT will always return a row, even for no rows in a table.

编辑，2012年4月：此规则在我的答案中进行了描述： COUNT（*）总是返回结果吗？

Edit, Apr 2012: the rules for this are described in my answer here:Does COUNT(*) always return a result?

您的计数/分配是正确的，但是可能是以下两种方式：

Your count/assign is correct but could be either way:

select @myInt = COUNT(*) from myTable
set @myInt = (select COUNT(*) from myTable)

但是，如果您只是寻找行的存在，（NOT）EXISTS效率更高：

However, if you are just looking for the existence of rows, (NOT) EXISTS is more efficient:

IF NOT EXISTS (SELECT * FROM myTable)

这篇关于将选择计数（*）值保存为整数（SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！