如何在mysql中选择具有相同值集的列? [英] How to select columns with same set of values in mysql?

问题描述

我的表格是:

patients(pid,name,city)

disease(did,dname)

has_disease(did,pid)

我想列出一组具有相同疾病的患者.pid和确实分别是患者和疾病表中的主键，并且是has_disease表中的外键.

I want to list the patients who have same set of diseases.pid and did are primary keys in patients and disease tables respectively and are foreign keys in has_disease table.

样本数据:

患者

pid    name   city

1      John    X

2      Jim     Y

3      Jack    Z

疾病

 did     dname

  1      Typhoid

  2      Malaria

  3      ViralFever

有疾病

 did     pid
  1       1
  1       2
  3       2
  1       3
  3       3

以上数据的答案是Jim and Jack，因为它们具有完全相同的疾病1和3，即疟疾和病毒热.我想知道如何在mysql中实现这一点.但这不起作用.

The answer for the above data is Jim and Jack because they have exactly the same set of diseases 1 and 3 namely malaria and viral fever.I want to know how to implement this in mysql.I tried relational division with where not exists but it doesn't work.

推荐答案

select p.*, GROUP_CONCAT(d.did SEPARATOR ', ') AS all_dids
from patients p 
     JOIN has_disease hd ON p.pid=hd.pid
     JOIN disease d ON d.did=hd.did
GROUP BY p.pid;

查询返回给我们患者及其疾病.

The query returns us patients and their diseases.

SELECT * 
FROM
    (select p.*, GROUP_CONCAT(d.did SEPARATOR ', ') AS all_dids
    from patients p 
         JOIN has_disease hd ON p.pid=hd.pid
         JOIN disease d ON d.did=hd.did
    GROUP BY p.pid) P1
    JOIN
    (select p.*, GROUP_CONCAT(d.did SEPARATOR ', ') AS all_dids
    from patients p 
         JOIN has_disease hd ON p.pid=hd.pid
         JOIN disease d ON d.did=hd.did
    GROUP BY p.pid) P2 ON p1.all_dids=p2.all_dids and p1.pid<>p2.pid

将2例患者的完整剂量列表进行比较，并让pid的剂量列表相同，而pid却不同

Compare 2 sets of patients by their full list of dids and leave pids with the same dids list but different pids

这篇关于如何在mysql中选择具有相同值集的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！