我如何比较同一个表中的 2 行 (SQL Server)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/510916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do i compare 2 rows from the same table (SQL Server)
提问by Andy Jones
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）
回答by Andy Jones
You can join a table to itself as many times as you require, it is called a self join.
您可以根据需要多次将表连接到自身，这称为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'
回答by Quassnoi
OK, after 2 years it's finally time to correct the syntax:
好的，2 年后终于到了更正语法的时候了：
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
回答by John Sansom
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
回答by Валентин Нэллин
SELECT COUNT(*) FROM (SELECT * FROM tbl WHERE id=1 UNION SELECT * FROM tbl WHERE id=2) a
SELECT COUNT(*) FROM (SELECT * FROM tbl WHERE id=1 UNION SELECT * FROM tbl WHERE id=2) a
If you got two rows, they different, if one - the same.
如果您有两行，则它们不同，如果有一行 - 相同。
回答by narender
SELECT * FROM A AS b INNER JOIN A AS c ON b.a = c.a
WHERE b.a = 'some column value'
