是否将子查询转换为MySQL的JOIN语句?

ShadowAccount • 5 年前 • 1250 次点击

以下是我当前的查询:

 SELECT prod.id,
       prod.title,
       prod.price,
       prod.status,
       (SELECT COUNT(*)
        FROM   payments
        WHERE  product = prod.id
               AND ( vendor = '1'
                     AND credited = 'Vendor' )
               AND ( status = 'Completed'
                      OR status = 'Pending'
                      OR status = 'Canceled_Reversal' )) AS sales,
       (SELECT SUM(price)
        FROM   payments
        WHERE  product = prod.id
               AND ( vendor = '1'
                     AND credited = 'Vendor' )
               AND ( status = 'Completed'
                      OR status = 'Pending'
                      OR status = 'Canceled_Reversal' )) AS revenue
FROM   products prod
WHERE  member = '1'
       AND status != 'Deleted' 
       AND status != 'Blocked'

现在我在主查询中使用两个子查询。

有没有什么方法可以使用JOIN语句来代替或简化查询以加快查询速度?

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/56388

1250 次点击

文章 [ 2 ] | 最新文章 5 年前

• 1 楼

Tim Biegeleisen 5 年前

对聚合使用联接:

SELECT
    prod.id,
    prod.title,
    prod.price,
    prod.status,
    COALESCE(t.sales, 0) AS sales,
    COALESCE(t.revenue, 0) AS revenue
FROM products prod
LEFT JOIN
(
    SELECT
        product,
        COUNT(*) AS sales,
        SUM(price) AS revenue
    FROM payments p
    WHERE vendor = '1' AND credited = 'Vendor' AND
          status IN ('Completed', 'Pending', 'Canceled_Reversal')
    GROUP BY product
) t
    ON t.product = prod.id;

注意,我们使用左边 products 表中甚至可能没有任何匹配的数据 payments COALESCE 报告销售和收入的零值。

子查询别名为 t 以上可能可以使用以下索引进行优化:

payments (product, vendor, credit, status, price)

• 2 楼

Gordon Linoff 5 年前

我想写得简单一点:

SELECT p.id, p.title, p.price, p.status,
       (SELECT COUNT(*)
        FROM payments pa
        WHERE pa.product = p.id AND
              pa.vendor = p.member AND
              pa.credited = 'Vendor' AND
              pa.status IN ('Completed', 'Pending', 'Canceled_Reversal')
      ) AS sales,
       (SELECT SUM(pa.price)
        FROM payments pa
        WHERE pa.product = p.id AND
              pa.vendor = p.member AND
              pa.credited = 'Vendor' AND
              pa.status IN ('Completed', 'Pending', 'Canceled_Reversal')
      ) AS revenue
FROM products p
WHERE p.member = 1 AND
      p.status NOT IN ('Deleted', 'Blocked');

这基本上是您的查询,具有更简单的别名和逻辑。

上有索引 payments(product, vendor, credit, status, price) ,这可能比其他方法更快。

另一种选择是:

SELECT p.id, p.title, p.price, p.status,
       COUNT(pa.product) as sales
       SUM(pa.price) as revenue
FROM products p LEFT JOIN
     payments pa
     ON pa.product = p.id AND
        pa.vendor = p.member AND
        pa.credited = 'Vendor' AND
        pa.status IN ('Completed', 'Pending',     
WHERE p.member = 1 AND
      p.status NOT IN ('Deleted', 'Blocked')

'已取消'

但是,子查询可能更快。

登录后回复