mysql - COUNT 和同一查询中的子 COUNT

Question

我有 2 个表:成员，订单。

Members: MemberID, DateCreated
Orders:  OrderID, DateCreated, MemberID

我想找出给定月份中新成员的数量，分割为订单组的数量，例如。 5+, 4, 3, 2, 1, 0

我有一个查询来计算成员的订单数量，但如何在一个查询中获得这些值？

SELECT
  COUNT(o.orderid) AS Purchases
FROM
  members m
  LEFT JOIN orders o ON o.memberid = m.memberid
    AND MONTH(o.DateCreated) = 8
WHERE
  MONTH(m.DateCreated) = 8
GROUP BY
  m.memberid
ORDER BY
  COUNT(o.orderid) DESC

Answer 1

有几种方法可以做到这一点，其中一些可能相当复杂。

这是我会做的方式，专注于新成员部分而不是计数部分:

  SELECT COUNT(M.MemberID),
         (SELECT COUNT(*) FROM Orders O WHERE O.MemberId = M.MemberId AND O.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)) AS num_orders
    FROM Members M
   WHERE M.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)
GROUP BY num_orders

我使用日期进行查找，因为它会更快(它可以使用索引，而 MONTH(M.DateCreated) 将始终进行全表扫描，但如果确实需要给定月份的所有订单/成员，您可以将其更改回来)。

编辑:
我忘了处理问题的 5+ 部分，所以这里有一个选项:

  SELECT COUNT(M.MemberID),
         (SELECT IF(COUNT(*) >= 5, '5+', COUNT(*)) FROM Orders O WHERE O.MemberId = M.MemberId AND O.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)) AS num_orders
    FROM Members M
   WHERE M.DateCreated BETWEEN '2010-08-01' AND DATE_ADD('2010-08-01', INTERVAL 1 MONTH)
GROUP BY num_orders