mysql - 用更有效的方法替换子选择计数

Question

我今天似乎有点兴奋，似乎无法想出更好的方法来对表格进行子选择计数。

基本上，我需要为每个不同的供应商做 2 项计数(来自同一张表)，一项用于分配给该供应商的总记录，另一项用于该供应商的有争议记录。目前我有下面的查询，它在技术上是正确的，但在有 1 个 mill + 记录的表上慢得要死。我确信有更好的方法来做这件事，但我今天早上无法解决这个问题。

SELECT 
DISTINCT tblsuppliers.SupplierName, 
(SELECT COUNT(*) FROM tblmovements a WHERE m.Supplier=a.Supplier AND a.TicketStatus IN(0,1) AND a.DateRequired>='2013-09-01 00:00:00' AND a.DateRequired<='2013-11-30 23:59:59') as 'Total Tickets',
(SELECT COUNT(*) FROM tblmovements b WHERE m.Supplier=b.Supplier AND b.TicketStatus IN(0,1) AND b.DateRequired>='2013-09-01 00:00:00' AND b.DateRequired<='2013-11-30 23:59:59' AND (b.SuppIsDisputed=1 OR b.SuppDisputeClearedBy>0)) as 'Total Disputed'
FROM tblmovements m 
INNER JOIN tblsuppliers ON m.Supplier=tblsuppliers.ID 
ORDER BY tblsuppliers.SupplierName ASC

连接表只是给我一个供应商名称，而不是存储在移动表中的供应商 ID。非常感谢任何建议。

Answer 1

试试这个:

SELECT s.SupplierName, 
       SUM(CASE WHEN m.TicketStatus IN(0,1) AND m.DateRequired>='2013-09-01 00:00:00' AND 
                     m.DateRequired<='2013-11-30 23:59:59' THEN 1 ELSE 0 
           END)  AS 'Total Tickets', 
       SUM(CASE WHEN m.TicketStatus IN(0,1) AND m.DateRequired>='2013-09-01 00:00:00' AND 
                     m.DateRequired<='2013-11-30 23:59:59' AND 
                    (m.SuppIsDisputed=1 OR m.SuppDisputeClearedBy>0) THEN 1 ELSE 0 
           END) AS 'Total Disputed'
FROM tblmovements m 
INNER JOIN tblsuppliers s ON m.Supplier=s.ID 
GROUP BY s.ID 
ORDER BY s.SupplierName ASC