SQL 选择具有 2 列相等值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7076766/
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
Select rows having 2 columns equal value
提问by Rohit
Consider following table
考虑下表
C1 || C2 || C3 || C4
--------------------------
1 || a || b || 1
2 || a || b || 4
3 || b || d || 2
4 || b || d || 2
Question 1: Select all rows in which Column C2 , C3, C4 have equal values e.g. select row 3 and row 4 in above example.
问题 1：选择列 C2、C3、C4 具有相等值的所有行，例如在上例中选择第 3 行和第 4 行。
Question 2: Select all rows in which C4 column has duplicates e.g. C4 has value 2 in row 3 and row 4, so select row 3 and 4.
问题 2：选择 C4 列有重复项的所有行，例如 C4 在第 3 行和第 4 行的值为 2，因此选择第 3 行和第 4 行。
回答by Curt
Question 1 query:
问题1查询：
SELECT ta.C1
,ta.C2
,ta.C3
,ta.C4
FROM [TableA] ta
WHERE (SELECT COUNT(*)
FROM [TableA] ta2
WHERE ta.C2=ta2.C2
AND ta.C3=ta2.C3
AND ta.C4=ta2.C4)>1
回答by dgmora
Actually this would go faster in most of cases:
实际上，在大多数情况下这会更快：
SELECT *
FROM table ta1
JOIN table ta2 on ta1.id != ta2.id
WHERE ta1.c2 = ta2.c2 and ta1.c3 = ta2.c3 and ta1.c4 = ta2.c4
You join on different rows which have the same values. I think it should work. Correct me if I'm wrong.
您加入具有相同值的不同行。我认为它应该工作。如果我错了纠正我。
回答by Rohit
Select * from tablename t1, tablename t2, tablename t3
where t1.C1 = t2.c2 and t2.c2 = t3.c3
Seems like this will work. Though does not seems like an efficient way.
似乎这会起作用。虽然似乎不是一种有效的方式。
回答by jsHate
SELECT *
FROM my_table
WHERE column_a <=> column_b AND column_a <=> column_c
回答by user2904660
For question 1:
对于问题 1：
SELECT DISTINCT a.*
FROM [Table] a
INNER JOIN
[Table] b
ON
a.C1 <> b.C1 AND a.C2 = b.C2 AND a.C3 = b.C3 AND a.C4 = b.C4
Using an inner join is much more efficient than a subquery because it requires fewer operations, and maintains the use of indexes when comparing the values, allowing the SQL server to better optimize the query before its run. Using appropriate indexes with this query can bring your query down to only n * log(n) rows to compare.
使用内部联接比子查询更有效，因为它需要更少的操作，并且在比较值时保持索引的使用，允许 SQL 服务器在运行之前更好地优化查询。在此查询中使用适当的索引可以将您的查询降低到仅 n * log(n) 行进行比较。
Using a subquery with your where clause or only doing a standard join where C1 does not equal C2 results in a table that has roughly 2 to the power of n rows to compare, where n is the number of rows in the table.
使用带有 where 子句的子查询或仅执行标准连接，其中 C1 不等于 C2 会生成一个表，该表大约有 2 的 n 次幂要比较，其中 n 是表中的行数。
So by using proper indexing with an Inner Join, which only returns records which met the join criteria, we're able to drastically improve the performance. Also note that we return DISTINCT a.*, because this will only return the columns for table a where the join criteria was met. Returning * would return the columns for both a and b where the criteria was met, and not including DISTINCT would result in a duplicate of each row for each time that row row matched another row more than once.
因此，通过对内部连接使用适当的索引，它只返回符合连接标准的记录，我们能够显着提高性能。另请注意，我们返回 DISTINCT a.*，因为这将只返回满足连接条件的表 a 的列。返回 * 将返回满足条件的 a 和 b 的列，并且不包括 DISTINCT 将导致每次该行与另一行匹配不止一次时每行重复。
A similar approach could also be performed using CROSS APPLY, which still uses a subquery, but makes use of indexes more efficiently.
也可以使用 CROSS APPLY 执行类似的方法，它仍然使用子查询，但可以更有效地使用索引。
An implementation with the keyword USING instead of ON could also work, but the syntax is more complicated to make work because your want to match on rows where C1 does not match, so you would need an additional where clause to filter out matching each row with itself. Also, USING is not compatible/allowed in conjunction with table values in all implementations of SQL, so it's best to stick with ON.
使用关键字 USING 而不是 ON 的实现也可以工作，但语法更复杂，因为您想要匹配 C1 不匹配的行，因此您需要一个额外的 where 子句来过滤掉匹配的每一行本身。此外，在所有 SQL 实现中，USING 不兼容/不允许与表值结合使用，因此最好坚持使用 ON。
Similarly, for question 2:
同样，对于问题 2：
SELECT DISTINCT a.*
FROM [Table] a
INNER JOIN
[Table] b
ON
a.C1 <> b.C1 AND a.C4 = b.C4
This is essentially the same query as for 1, but because it only wants to know which rows match for C4, we only compare on the rows for C4.
这本质上与 1 的查询相同，但因为它只想知道哪些行与 C4 匹配，所以我们只比较 C4 的行。
回答by Safeer Malik
SELECT t1.* FROM table t1 JOIN table t2 ON t1.Id=t2.Id WHERE t1.C4=t2.C4;
Giving Accurate Result for me.
为我提供准确的结果。
回答by a'r
select t.* from table t
join (
select C2, C3, C4
from table
group by C2, C3, C4
having count(*) > 1
) t2
using (C2, C3, C4);
回答by Kracekumar
select * from test;
a1 a2 a3
1 1 2
1 2 2
2 1 2
select t1.a3 from test t1, test t2 where t1.a1 = t2.a1 and t2.a2 = t1.a2 and t1.a1 = t2.a2
a3
1
You can try same thing using Joins too..
您也可以使用 Joins 尝试同样的事情..
