SQL查询在多对多关系中的精确匹配 [英] SQL Query for exact match in many to many relation

问题描述

我有以下表格(仅列出必需的属性)

I have the following tables(only listing the required attributes)

1. 药物(ID，名称)
2. 通用(ID，名称)
3. med_gen(med_id引用了药物(id)，gen_id引用了通用药物(id)，效力)

样本数据

药物

1. (1，'Crocin')
2. (2，'Stamlo')
3. (3，'NT Kuf')

通用

1. (1，'六氯丁')
2. (2，苯甲酸甲酯")

med_gen

1. (1，1，'100mg')
2. (1、2，'50毫升')
3. (2，1，'100mg')
4. (2，2，'60ml')
5. (3，1，'100mg')
6. (3，2，'50ml')

我想要所有等同于给定药物的药物.这些药物具有相同的通用性和相同的效力，彼此等效.在上面的示例数据中，所有三个都具有相同的泛型，但是只有1和三个对相应的泛型也具有相同的效力.所以1和3是等效的药物.

I want all the medicines which are equivalent to a given medicine. Those medicines are equivalent to each other that have same generic as well as same potency. In the above sample data, all the three have same generics, but only 1 and three also have same potency for the corresponding generics. So 1 and 3 are equivalent medicines.

我想找出具有药物编号的等效药物.

I want to find out equivalent medicines given a medicine id.

注意:一种药物可能具有多种仿制药. Medicine表大约有102000条记录，generic表大约有2200条记录，potency表大约有200000条记录.因此，性能是关键.

NOTE : One medicine may have any number of generics. Medicine table has around 102000 records, generic table around 2200 and potency table around 200000 records. So performance is a key point.

注2:MySQL中使用的数据库.

推荐答案

在MySQL中做到这一点的一种方法是利用GROUP_CONCAT()函数

One way to do it in MySQL is to leverage GROUP_CONCAT() function

SELECT g.med_id
  FROM 
(
  SELECT med_id, GROUP_CONCAT(gen_id ORDER BY gen_id) gen_id, GROUP_CONCAT(potency ORDER BY potency) potency
    FROM med_gen
   WHERE med_id = 1 -- here 1 is med_id for which you're trying to find analogs
) o JOIN 
(
  SELECT med_id, GROUP_CONCAT(gen_id ORDER BY gen_id) gen_id, GROUP_CONCAT(potency ORDER BY potency) potency
    FROM med_gen
   WHERE med_id <> 1 -- here 1 is med_id for which you're trying to find analogs
   GROUP BY med_id 
) g
 ON o.gen_id = g.gen_id
AND o.potency = g.potency

输出:

| MED_ID |
|--------|
|      3 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于SQL查询在多对多关系中的精确匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！