MYSQL select count 关系划分

Question

我在mysql中有这张表 1. 是否可以选择一个计数 - 所有相同的 entity_id where field_tags_tid=2 and field_tags_tid=7

在此示例中，结果将为 1，因为只有 entity_id=6 匹配 field_tags_tid=2 和 field_tags_tid=7

Answer 1

这个问题常被称为关系划分

SELECT  entity_ID
FROM    tableName
WHERE   field_tags_ID IN (2,7)
GROUP   BY entity_ID
HAVING  COUNT(*) = 2

如果没有对每个 entity_ID 的 field_tags_ID 强制执行唯一性，则需要一个 DISTINCT 关键字。否则，保持原样，

SELECT  entity_ID
FROM    tableName
WHERE   field_tags_ID IN (2,7)
GROUP   BY entity_ID
HAVING  COUNT(DISTINCT field_tags_ID) = 2

• SQLFiddle Demo
• SQL of Relational Division

更新 1

SELECT  COUNT(*) totalCOunt
FROM
(
    SELECT  entity_ID
    FROM    tableName
    WHERE   field_tags_tid IN (2,7)
    GROUP   BY entity_ID
    HAVING  COUNT(DISTINCT field_tags_tid) = 2
) s

• SQLFiddle Demo