mysql - HAVING COUNT 接收其他 COUNT 值

Question

我正在两个不同的表中进行选择，这些表应按组引用 ID 进行匹配，例如:表 1 和表 2:

+-----+-----+------------+   +-----+------+
| gid | tid | created    |   | gid | nid  |
+-----+-----+------------+   +-----+------+
|   0 | 816 | 1480002041 |   |   0 | 1123 |
|   1 | 731 | 1480003932 |   |   0 | 1124 |
|   1 | 736 | 1480003932 |   |   1 | 1125 |
|   2 | 746 | 1480003932 |   |   1 | 1126 |
+-----+-----+------------+   |   1 | 1123 |
                             |   2 | 1124 |
                             |   1 | 1129 |
                             +-----+------+

我需要从表 2 获取与表 1 上的组完全匹配的 nid 值。 表 1 中搜索的引用是 tid。我相信 SQL 会是这样的:

SELECT  t1.nid
FROM table1 t1
INNER JOIN
(
    SELECT  gid 
    FROM    table2 
    WHERE   tid IN (731, 736, 746, 751)
    GROUP   BY gid
    HAVING  COUNT(DISTINCT tid) = 4
) t2 ON t1.gid = t2.gid;

但是我怎样才能得到准确的计数来替换硬编码的数字4？

Answer 1

SELECT t2.nid
  FROM table1 t1
INNER JOIN
  table2 t2
  ON t1.gid = t2.gid;
WHERE t2.tid IN (731, 736, 746, 751)
  GROUP BY t2.gid