IF语句中的SQL SELECT语句

I have a trigger in SQL Server that needs to check on an update the number of rows with a value between a certain amount and do something accordingly. My current code is something like this:

我在SQL Server中有一个触发器,它需要在更新时检查具有一定数量值的行数,并相应地执行某些操作。我目前的代码是这样的:

IF EXISTS(SELECT COUNT(id) as NumberOfRows
          FROM database
          WHERE id = 3 AND value <= 20 and value > 2
          GROUP BY id
          HAVING COUNT(id) > 18)
    -- if true, do something

From what I can tell, the select statement should find the number of rows with a value between 2 and 20 and if there are more than 18 rows, the EXISTS function should return 1 and the query will execute the code within the IF statement.

据我所知,select语句应该找到值在2到20之间的行数,如果有超过18行,EXISTS函数应返回1,查询将执行IF语句中的代码。

However, what is happening is that it is always executing the code within the IF statement regardless of the number of rows with a value between 2 and 20.

但是,正在发生的是它始终在IF语句中执行代码,而不管值在2到20之间的行数。

Any ideas on why this might be? I can post more complete code if it might help.

关于为什么会这样的任何想法?如果可能有帮助,我可以发布更完整的代码。

2 个解决方案

#1

The reason is that the Exists function is checking the result of the sub-query for existing - are there any rows or not. And, as you return the COUNT, it'll never be not-existing - COUNT returns 0 if there are no rows presented in database.

原因是Exists函数正在检查现有子查询的结果 - 是否存在任何行。并且,当您返回COUNT时,它将永远不会存在 - 如果数据库中没有显示行,COUNT将返回0。

Try to store the resulting count in a local variable, like in this question:

尝试将结果计数存储在局部变量中,如下所示:

Using IF ELSE statement based on Count to execute different Insert statements

使用基于Count的IF ELSE语句执行不同的Insert语句

DECLARE @retVal int

SELECT @retVal = COUNT(*) 
FROM TABLE
WHERE COLUMN = 'Some Value'

IF (@retVal > 0)
BEGIN
    --INSERT SOMETHING
END
ELSE
BEGIN
    --INSERT SOMETHING ELSE
END

#2

Your subquery is looking for matches in the entire table. It does not limit the results only to those that are related to the rows affected by the update. Therefore, if the table already has rows matching your condition, the condition will be true on any update that affects other rows.

您的子查询正在整个表中查找匹配项。它不会仅将结果限制为与受更新影响的行相关的结果。因此,如果表已经具有与您的条件匹配的行,则在影响其他行的任何更新上条件都将为true。

In order to count only the relevant rows, you should either join the database table to the inserted pseudo-table or use just the inserted table (there is not enough information in your question to be sure which is better).

为了只计算相关的行,您应该将数据库表连接到插入的伪表,或者只使用插入的表(在您的问题中没有足够的信息以确定哪个更好)。

#1