mysql - SELECT * FROM Table WHERE colA 和 colB 至少有 3 个相同

Question

我必须将这两个 mySQL 查询合并为一个。我复制了一个解决方案并将其用于连接表。我正在查询一个包含两列(标记为“to_”和“from_”)的连接表。 'to_' 和 'from_' 都保存同一张表的 ID 号。我需要以这样的方式组合这些查询，以便根据以下条件获得结果:[('from_' + 'to_') > 3]，其中 'from_' 和 'to_' 具有相同的值(即，它们指的是相同的 ID)。

$query = "select * from nodes where nodeID in (
    select to_ from joinTable group by to_ having count(*) > 3
)";

...
$query = "select * from nodes where nodeID in (
    select from_ from joinTable group by from_ having count(*) > 3
)";

致谢:我使用的查询非常接近“E 先生”之前帮我解决的解决方案。

Answer 1

您可以尝试(请参阅最后一段关于 to_ 和 from_ 匹配要求的重要通知):

SELECT X.to_, COUNT(*) 
FROM joinTable X, joinTable Y
WHERE
    X.to_ = Y.from_
GROUP BY X.to_
HAVING COUNT(*) > 2

或者

SELECT X.to_, COUNT(*) 
FROM joinTable X LEFT JOIN joinTable Y ON X.to_ = Y.from_
GROUP BY X.to_
HAVING COUNT(*) > 2

使用 Mr E的测试数据:

CREATE TABLE `foo` (
  `id` int(1) DEFAULT NULL,
  `to_` varchar(5) DEFAULT NULL,
  `from_` varchar(5) DEFAULT NULL
);

INSERT INTO `foo` VALUES (1,'A','B'),(2,'B','A'),(3,'B','C'),(4,'X','C'),(4,'X','B');

它会通过发出:

SELECT X.to_, Y.from_
FROM foo X LEFT JOIN foo Y ON X.to_ = Y.from_

然后会产生:

mysql> SELECT X.to_, Y.from_ /*--, COUNT(*) */
    -> FROM foo X LEFT JOIN foo Y ON X.to_ = Y.from_;
+------+-------+
| to_  | from_ |
+------+-------+
| A    | A     | 
| B    | B     | 
| B    | B     | 
| B    | B     | 
| B    | B     | 
| X    | NULL  | 
| X    | NULL  | 
+------+-------+
7 rows in set (0.00 sec)

并通过完整运行:

mysql> SELECT X.to_, COUNT(*)
    -> FROM foo X LEFT JOIN foo Y ON X.to_ = Y.from_
    -> GROUP BY X.to_
    -> HAVING COUNT(*) > 2;
+------+----------+
| to_  | COUNT(*) |
+------+----------+
| B    |        4 | 
+------+----------+

基本上，将表与其自身连接，然后从两个表中生成一个 N:N 匹配记录列表，其中 to_ 和 from_ 匹配(无论是否在同一行)，然后使用单个列并聚合其值最后的计数(*)。

而且，最重要的是，为什么我将 HAVING COUNT(*) 上的数字从 3 降低到 2？ N:N 关系将发出 N1 * N2 条记录(其中 N1 是第一个表中匹配记录的计数，N2 是第二个表中的匹配记录数)。因此，如果下限是三，我们只能在这两个表上有超过 3 条记录，方法是在其中一个表中有一个记录，然后在另一个表中有 3 个记录(以任何顺序)，或者一个表中有 2 个记录，另一个表中有 2 个记录(然后从那里开始) - 否则 to_ 和 from_ 字段上将没有匹配项，这是我不确定的事情 - OP 是否只想要其值出现在两个字段上的记录，或者是否有 COUNT(*)从一个方面就足够了。但是，如果是后者，除了将查询分开以单独处理每一列之外，我看不到任何其他选项，正如一些人已经发布的那样，因为这是我们正在处理的孤立的总和。如果针对大表运行，这将慢。