SQL查询优化：如何过滤掉订单数为0的记录？

给你两种可行的实现方式，都能过滤掉订单数（orders）为0的记录，只保留有订单的行：

方式一：基于原查询添加HAVING过滤 #

直接在原查询末尾加上HAVING orders > 0即可，因为orders是查询生成的计算列，没法用WHERE直接过滤，HAVING可以对计算后的列做筛选：

SELECT customers.id, customers.companyname, customers.orgtype,
   (SELECT COALESCE(SUM(invoicetotal), 0)
    FROM invoice_summary
    WHERE invoice_summary.cid = customers.ID 
    and invoice_summary.submitted between '2022-08-01' and '2022-08-31'
    ) AS total,
    (SELECT COUNT(invoicenumber)
    FROM invoice_summary
    WHERE invoice_summary.cid = customers.ID 
    and invoice_summary.submitted between '2022-08-01' and '2022-08-31'
    ) AS orders
FROM customers 
WHERE customers.orgtype = 10
HAVING orders > 0
ORDER BY total DESC

方式二：优化查询结构（推荐，性能更好） #

原查询里两个子查询重复扫描了invoice_summary表，换成JOIN分组统计的方式，只扫描一次统计表，效率更高，同时直接过滤有订单的记录：

SELECT c.id, c.companyname, c.orgtype,
       COALESCE(s.total, 0) AS total,
       COALESCE(s.orders, 0) AS orders
FROM customers c
LEFT JOIN (
    SELECT cid,
           SUM(invoicetotal) AS total,
           COUNT(invoicenumber) AS orders
    FROM invoice_summary
    WHERE submitted BETWEEN '2022-08-01' AND '2022-08-31'
    GROUP BY cid
) s ON s.cid = c.id
WHERE c.orgtype = 10
  AND s.orders > 0
ORDER BY total DESC

最终效果 #

两种方式都会返回以下结果：

内容的提问来源于stack exchange，提问作者Dave