mysql - 如何更改SQL以避免子查询

这是我工作的queryie的简化版本：

SELECT
    r.id,
    r.nr,

    MAX(CASE WHEN (rm.meta_key = 'supplier_id') THEN (SELECT suppliers.title FROM suppliers WHERE suppliers.id = rm.meta_value) ELSE NULL END) AS supplier,
    MAX(CASE WHEN (rm.meta_key = 'client_id') THEN (SELECT clients.name FROM clients WHERE clients.id = rm.meta_value) ELSE NULL END) AS client,

FROM `registries` AS r
INNER JOIN `registries_meta` AS rm ON `r`.`id` = `rm`.`registries_id`
GROUP BY r.id
LIMIT 100

在这里可以避免子查询吗？我需要告诉Mysql“Join registries_metas，如果meta_key是client_id Join clients.id=meta_value并选择clients.name”。
谢谢。

最佳答案

我相信你想要的逻辑只是

SELECT r.id, r.nr,
       c.name as client_name, s.title as supplier
FROM registries r INNER JOIN
     registries_meta rm
     ON r.id = rm.registries_id LEFT JOIN
     clients c
     ON rm.meta_value = c.id AND rm.meta_key = 'client_id' LEFT JOIN
     suppliers s
     ON rm.meta_value = s.id AND rm.meta_key = 'supplier_id';

如果您可以有多个客户/供应商，那么您可能希望所有客户/供应商都在同一行。这意味着聚合：

SELECT r.id, r.nr,
       GROUP_CONCAT(c.name) as client_names,
       GROUP_CONCAT(s.title) as suppliers
FROM registries r INNER JOIN
     registries_meta rm
     ON r.id = rm.registries_id LEFT JOIN
     clients c
     ON rm.meta_value = c.id AND rm.meta_key = 'client_id' LEFT JOIN
     suppliers s
     ON rm.meta_value = s.id AND rm.meta_key = 'supplier_id'
GROUP BY r.id, r.nr;

关于mysql - 如何更改SQL以避免子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52577277/