如何更改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 "Join 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;

Answer 2

与客户和供应商表进行左连接

SELECT 
    r.id,
    r.nr,
    c.name AS client_name,s.title as supplier
FROM `registries` AS r 
INNER JOIN `registries_meta` AS rm ON `r`.`id` = `rm`.`registries_id`
left join clients c on rm.meta_value=c.id 
left join suppliers s on rm.meta_value=suppliers.id

Answer 3

尝试向左连接到clients表。我的直觉告诉我，我们需要对子查询进行透视，以便为每个键找到正确的元值。然后，我们可以使用该元值(应该是客户端ID)左连接到clients。

SELECT
    t.id,
    t.nr,
    t.client AS client_id,
    COALESCE(c.name, '') AS client_name,
    t.supplier AS supplier_id,
    COALESCE(s.title, '') AS supplier_title
FROM
(
    SELECT 
        r.id,
        r.nr,
        MAX(CASE WHEN rm.meta_key = 'client_id' THEN rm.meta_value END) AS client,
        MAX(CASE WHEN rm.meta_key = 'supplier_id' THEN rm.meta_value END) AS supplier
    FROM registries AS r 
    INNER JOIN registries_meta AS rm
        ON r.id = rm.registries_id
    GROUP BY r.id
) t
LEFT JOIN clients AS c
    ON t.client = c.id
LEFT JOIN suppliers AS s
    ON t.supplier = s.id;