两个sql语句有什么区别？

Good Day

All we are trying to do is inside a trigger make sure the user is not inserting two fees that have 'alone' in the name. Those fees need to be handled individually.

我们所要做的就是在触发器内部确保用户不会插入两个名称中“单独”的费用。这些费用需要单独处理。

For some reason, it appears the top section of sql quit working two weeks ago. To get around it I recoded it the second way and get the correct results. What I am confused is why would the first portion seemed to have worked for the last several years and now it does not?

出于某种原因,它出现在两周前sql quit工作的顶部。为了解决它,我以第二种方式重新编码并获得正确的结果。令我感到困惑的是,为什么第一部分似乎在过去几年中起作用,现在却没有?

SELECT  @AloneRecordCount = count(*) 

FROM  inserted i 
      INNER JOIN deleted d on i.id = d.id 

WHERE  i.StatusID = 32 
      AND d.StatusID <> 32 
      AND i.id IN  
      (SELECT settlementid FROM vwFundingDisbursement fd
      WHERE fd.DisbTypeName LIKE  '%Alone'
      AND fd.PaymentMethodID = 0)


SELECT  @AloneRecordCount = count(i.id) 

FROM    inserted i INNER JOIN
        deleted d on i.id = d.id
        JOIN vwFundingDisbursement fd on i.id = fd.settlementid

WHERE   i.StatusID = 32 
        AND d.StatusID <> 32  
        AND fd.DisbTypeName like '%Alone'
        AND fd.PaymentMethodID = 0

this is on SQL Server 2005
there is no error, instead the top statement will only return 1 or zero
while the bottom statement will return the actual number found.

这是在SQL Server 2005上没有错误,而top语句只返回1或0,而bottom语句将返回找到的实际数字。

4 个解决方案

#1

A schema (or at least how the view is created) would help, but here's a guess...

架构(或至少如何创建视图)会有所帮助,但这是一个猜测......

If you are looking for multiple rows in vwFundingDisbursement with the value "Alone" in the distribution type name, then a JOIN is going to return multiple rows as your source table (INSERTED) joins to multiple rows in the view. If you use IN though, SQL doesn't care if it returns multiple matches, it's only going to give you one row.

如果要在vwFundingDisbursement中查找分配类型名称中值为“Alone”的多行,则当源表(INSERTED)连接到视图中的多行时,JOIN将返回多行。如果你使用IN,SQL不关心它是否返回多个匹配,它只会给你一行。

As an example:

举个例子:

CREATE TABLE dbo.Test_In_vs_Join1
(
     my_id     INT     NOT NULL
)

CREATE TABLE dbo.Test_In_vs_Join2
(
     my_id     INT     NOT NULL
)

INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (1)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (2)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (3)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (4)
INSERT INTO dbo.Test_In_vs_Join1 (my_id) VALUES (5)

INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (1)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (1)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (2)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (3)
INSERT INTO dbo.Test_In_vs_Join2 (my_id) VALUES (3)

SELECT
     T1.my_id,
     COUNT(*)
FROM
     dbo.Test_In_vs_Join1 T1
INNER JOIN dbo.Test_In_vs_Join2 T2 ON
     T2.my_id = T1.my_id
GROUP BY
    T1.my_id

SELECT
     T1.my_id,
     COUNT(*)
FROM
     dbo.Test_In_vs_Join1 T1
WHERE
    T1.my_id IN (SELECT T2.my_id FROM dbo.Test_In_vs_Join2 T2)
GROUP BY
    T1.my_id

On a side note, burying a column inside of another column like this is a violation of the normalized form and just asking for problems. Performing this kind of business logic in a trigger is also a dangerous path to go down as you're finding out.

另一方面,将列嵌入另一列内部是违反规范化形式的,只是提出问题。在触发器中执行这种业务逻辑也是一条危险的路径,可以在您发现时向下移动。

#2

Have you got a null vwFundingDisbursement.settlementid value? Has that appeared recently?

你有一个null的vwFundingDisbursement.settlementid值吗?这最近出现了吗?

#3

count(*)
count(i.id)

will return different counts based on NULL values

将根据NULL值返回不同的计数

In SQL, what’s the difference between count(*) and count('x')?

在SQL中,count(*)和count('x')之间有什么区别?

#4

I assume this MSSQL(?) and it is handling an UPDATE statement since you're joining both the inserted/deleted tables?

我假设这个MSSQL(?)并且它正在处理UPDATE语句,因为你要加入插入/删除的表?

Which table is this trigger on?

这个触发器在哪个表上?

#1