mysql子查询无限期运行

Question

我有两个表:我正在执行连接并希望返回一个查询，其中包含为每个 GenEx 处方品牌列出的多个代号。但是，看起来我执行连接的方式会导致它超时。

药物:

ID | GenEx   | CodeName | Desc
----------------------------
1  | Cipro   | Dolvo    | 
2  | Ludavil | Ymir     | 
3  | Cipro   | Alpha    |

医药:

   ID | GenEx    | Price    | 
    ----------------------------
    1  | Cipro   | 4.99     | 
    2  | Ludavil | 12.99    | 
    3  | Benazol | 5.00     |

我想回来:

1. GenEx->Cipro, CodeName=>Dolvo,Alpha, Price->4.99
2. GenEx->Ludavil, CodeName=>Ymir, Price->12.99

myquery 永远不会完成:

SELECT GenEx, Price
 GROUP_CONCAT(CodeName) as CodeName
 FROM (`Drugs` d) 
 JOIN `Medicine` m ON `m`.`GenEx` = `d`.`GenEx`
 WHERE GenEx
 IN (
 SELECT DISTINCT GenEx
   FROM Drugs
   WHERE codeName IN ('Alpha'))
 ) 
  GROUP BY `GenEx`;

Answer 1

现在根据问题的最后更新更新答案。

试试这个代码:

SELECT d.`GenEx`, d.`CodeName`, d.`Price`,
 GROUP_CONCAT(d.`CodeName`) as CodeName
 FROM Drugs d
 JOIN Medicine m 
 ON m.`GenEx` = d.`GenEx`
 AND d.`GenEx`
 IN (
 SELECT DISTINCT `GenEx`
   FROM drugs
   WHERE codeName IN ('Alpha'))
 )
  GROUP BY d.`GenEx`;

让我知道你现在得到了什么。