mysql - 加入 select 内的 where 子句

Question

我正在尝试提供与 this one 类似的解决方案- 我所拥有的是 JOIN 内的 SELECT 查询，问题是该查询对所有行都满负荷运行(我说的是每个表 60,000 行 - 它在 3 个表上运行!)。

所以我想要做的就是向 JOIN 内的 SELECT 添加一个 WHERE 子句。但是，我无法访问外部 SELECT 并获得我需要的正确 WHERE 条件。我尝试的查询在这里:

SELECT  c.compete_id AS id,
      s.id AS store_id,
      c.enabled AS enabled,
      s.store_name AS store_name,
      s.store_url AS store_url,
      c.verified AS verified,
      r.rating_total AS rating,
      r.positive_percent AS percent,
      r.type AS type
FROM compete_settings c
LEFT JOIN stores s
    ON c.compete_id = s.id
LEFT JOIN (
    (SELECT store_id, rating_total, positive_percent, 'ebay' AS type FROM ebay_sellers WHERE store_id = c.compete_id)
    UNION
    (SELECT store_id, rating_total, positive_percent, 'amazon' AS type FROM amazon_sellers WHERE store_id = c.compete_id)
    UNION
    (SELECT store_id, CASE WHEN rank = 0 THEN NULL ELSE (200000 - rank) END AS rating_total, '100' as positive_percent, 'alexa' AS type FROM alexa_ratings WHERE store_id = c.compete_id)
) AS r
ON c.compete_id = r.store_id
    WHERE c.store_id = :store_id

注意，:store_id 是通过框架绑定(bind)的变量 - 让我们假设它是数字 12345。

我该怎么做？有什么想法吗？

Answer 1

我们最终采用了不同的方法 - 我们只是加入了所有内容，并且只选择了带有 CASE 的正确列。这是最终的查询:

SELECT  c.id AS id,
      s.id AS store_id,
      c.enabled AS enabled,
      s.store_name AS store_name,
      s.store_url AS store_url,
      c.verified AS verified,
      (CASE WHEN eb.rating_total IS NOT NULL THEN eb.rating_total
            WHEN am.rating_total IS NOT NULL THEN am.rating_total
            WHEN ax.rank IS NOT NULL THEN ax.rank
      END) AS rating,
      (CASE WHEN eb.positive_percent IS NOT NULL THEN eb.positive_percent
            WHEN am.positive_percent IS NOT NULL THEN am.positive_percent
            WHEN ax.rank IS NOT NULL THEN '100'
      END) AS percent,
      (CASE WHEN eb.positive_percent IS NOT NULL THEN 'ebay'
            WHEN am.positive_percent IS NOT NULL THEN 'amazon'
            WHEN ax.rank IS NOT NULL THEN 'alexa'
      END) AS type
FROM compete_settings c
LEFT JOIN stores s
  ON c.compete_id = s.id
LEFT JOIN ebay_sellers eb ON c.compete_id = eb.store_id
LEFT JOIN amazon_sellers am ON c.compete_id = am.store_id
LEFT JOIN alexa_ratings ax ON c.compete_id = ax.store_id
WHERE c.store_id = :store_id