问行和表之间的关系- mySQL

EN

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

SELECT CASE WHEN A.COUNT = B.FOUND_DISES THEN M.DISEASE_NAME ELSE 'NOT FOUND' END AS     DISEASE
FROM
(
SELECT COUNT(*) AS COUNT, DISEASE_ID
FROM MS_DISEASES_CHARACTERISTICS
GROUP BY DISEASE_ID
) A
JOIN
(
SELECT COUNT(*) AS FOUND_DISES, DISEASE_ID
FROM MS_DISEASES_CHARACTERISTICS
WHERE CHARACTERISTIC_ID IN (<VALUE YOU PASS>)
GROUP BY DISEASE_ID
) B
ON WHERE A.DISEASE_ID = B.DISEASE_ID
JOIN MS_DISEASES AS M
ON D.ID = A.DISEASE_ID

SELECT disease_name
FROM ms_diseases
WHERE id = (SELECT disease_id FROM (
  SELECT disease_id, count(disease_id) CT
  FROM 
      ms_diseases_characteristics AS dc,
      ms_characteristics AS c 
  WHERE
      c.id = dc.characteristic_id AND
      c.id IN (1, 2)
  GROUP BY disease_id
  ORDER BY CT DESC) temp LIMIT 1);

SELECT disease_name
FROM ms_diseases
WHERE id = (SELECT disease_id FROM (
  SELECT disease_id, count(disease_id) CT
  FROM 
      ms_diseases_characteristics AS dc,
      ms_characteristics AS c 
  WHERE
      c.id = dc.characteristic_id AND
      c.characteristic_name IN ('Headache','Frequent pain')
  GROUP BY disease_id
  ORDER BY CT DESC) temp LIMIT 1);