php - 行和表的关系 - mySQL

Question

我正在开发一个 PHP + MySQL 项目，需要知道如何进行 SQL 查询。下面是一张表格草图:

http://s24.postimg.org/na86w4do4/a_1.jpg

ms_diseases 表存储疾病名称。ms_characteristics 存储可能来自某种疾病的特征。ms_diseases_characteristics 表交叉了疾病和特征之间的关系

这是一个例子:

http://s21.postimg.org/sg1f7kfti/b_1.jpg

“偏头痛”疾病 (ms_diseases) 在 ms_diseases_characteristics 表中具有相关的数字 1 和 2 特征。

现在的问题是:我如何使用值 1 和 2 进行查询，仅返回具有 1 和 2 特征的疾病？那在特定情况下是“偏头痛”吗？

示例:

Characteristics – 1, 2 – return -> MIGRAINE
Characteristics – 1 – return > NOTHING
Characteristics – 1, 4, 5 – return > FLU
Characteristics – 1, 4 – return > NOTHING

如何查询返回具有此类特征的疾病？

Answer 1

SELECT disease_name
FROM
(
    SELECT  a.disease_name, c.totalCount
    FROM    ms_diseases a
            INNER JOIN ms_diseases_characteristics b
                ON  a.ID = b.disease_ID 
            INNER JOIN 
            (
                SELECT  disease_ID, COUNT(*) totalCount
                FROM    ms_diseases_characteristics
                GROUP   BY disease_ID
            ) c ON  b.disease_ID  = c.disease_ID 
    WHERE   b.characteristic_id IN (1,4,5)         -- <<== list of charateristics
    GROUP   BY a.disease_name                                       ^^
    HAVING  COUNT(*) = c.totalCount AND                             ^^
            COUNT(DISTINCT b.characteristic_id) = 3 -- << # of parameters
) s

• SQLFiddle Demo