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

Question

这是我正在工作的 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“加入 registries_metas 并且如果 meta_key 是 client_id JOIN clients.id = meta_value 并选择 clients.name”。

谢谢。

Answer 1

我相信您想要的逻辑只使用 join:

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;