如何比较来自同一表的2行(SQL Server)

I need to create a background job that processes a table looking for rows matching on a particular id with different statuses. It will store the row data in a string to compare the data against a row with a matching id.

我需要创建一个后台作业，该作业处理查找具有不同状态的特定id上匹配的行。它将把行数据存储在字符串中，以便将数据与具有匹配id的行进行比较。

I know the syntax to get the row data but i have never tried comparing 2 rows from the same table before? How is it done? Would i need to use variables to store the data from each? Or some other way?

我知道获取行数据的语法，但是我从来没有尝试过比较同一表中的2行。它是如何做的?我是否需要使用变量来存储每个变量的数据?还是其他方式?

(Using SQL Server 2008)

(使用SQL Server 2008)

5 个解决方案

#1

You can join a table to itself as many times as you require, it is called a self join.

您可以任意地将一个表连接到它本身，它被称为自连接。

An alias is assigned to each instance of the table (as in the example below) to differentiate one from another.

将一个别名分配给表的每个实例(如下面的示例所示)，以区分一个实例和另一个实例。

SELECT a.SelfJoinTableID
FROM   dbo.SelfJoinTable a
       INNER JOIN dbo.SelfJoinTable b
         ON a.SelfJoinTableID = b.SelfJoinTableID
       INNER JOIN dbo.SelfJoinTable c
         ON a.SelfJoinTableID = c.SelfJoinTableID
WHERE  a.Status = 'Status to filter a'
       AND b.Status = 'Status to filter b'
       AND c.Status = 'Status to filter c'

#2

OK, after 2 years it's finally time to correct the syntax:

好吧，两年后，我们终于该纠正语法了:

SELECT  t1.value, t2.value
FROM    MyTable t1
JOIN    MyTable t2
ON      t1.id = t2.id
WHERE   t1.id = @id
        AND t1.status = @status1
        AND t2.status = @status2

#3

Some people find the following alternative syntax easier to see what is going on:

有些人发现下面的替代语法更容易理解:

select t1.value,t2.value
from MyTable t1
    inner join MyTable t2 on
        t1.id = t2.id
where t1.id = @id

#4

SELECT COUNT(*) FROM (SELECT * FROM tbl WHERE id=1 UNION SELECT * FROM tbl WHERE id=2) a

选择COUNT(*) FROM (SELECT * FROM tbl，其中id=1 UNION SELECT * FROM tbl，其中id=2) a

If you got two rows, they different, if one - the same.

如果你有两行，它们是不同的，如果一行是一样的。

#5

SELECT * FROM A AS b INNER JOIN A AS c ON b.a = c.a
WHERE b.a = 'some column value'

#1