MySQL查找计数按月分组

Question

就我的问题而言，这些表格的总体结构如下：

• 工人分布在不同的部门(分支表)。
• 潜在客户登记(登记表)为客户(客户表)
• 并可订购产品购买(订购表)。

分支表：

+------------+--------------+-----------------+
| 'branchId' | 'street'     | 'city'          |
+------------+--------------+-----------------+
| 'B002'     | 'Clover Dr'  | 'London'        |
| 'B003'     | 'Main St'    | 'Glagsow'       |
| 'B004'     | 'Manse Rd'   | 'Bristol'       |
| 'B005'     | 'Deer Rd'    | 'London'        |
| 'B007'     | 'Argyll St'  | 'Los Angeles'   |
| 'B008'     | 'Mission St' | 'San Francisco' |
| 'B009'     | 'SOMA'       | 'San Francisco' |
+------------+--------------+-----------------+

顾客表：

+--------------+----------+-----------+-----------------+
| 'customerId' |  'fName' |  'lName'  |  'telNo'        |
+--------------+----------+-----------+-----------------+
| 'CR56'       | 'Aline'  | 'Stewart' | '0141-848-1825' |
| 'CR58'       | 'Jacky'  | 'Ho'      | '0123-1325434'  |
| 'CR62'       | 'Mary'   | 'Tregar'  | '01224-196720'  |
| 'CR74'       | 'Mike'   | 'Ritchie' | '01475-392178'  |
| 'CR76'       | 'John'   | 'Kay'     | '0207-774-5632' |
+--------------+----------+-----------+-----------------+

登记表：

+--------------+------------+------------+-----------------------+
| 'customerId' | 'branchId' | 'workerId' | 'joiningDate'         |
+--------------+------------+------------+-----------------------+
| 'CR56'       | 'B003'     | 'SG37'     | '2004-05-02 12:00:00' |
| 'CR58'       | 'B003'     | 'SA9'      | '2004-05-03 12:00:00' |
| 'CR62'       | 'B007'     | 'SA9'      | '2004-05-01 12:00:00' |
| 'CR74'       | 'B004'     | 'SG37'     | '2004-04-04 12:00:00' |
| 'CR76'       | 'B005'     | 'SL41'     | '2004-03-03 12:00:00' |
+--------------+------------+------------+-----------------------+

订购表：

+--------------+---------------+-----------------------+
| 'customerId' |  'productId' |  'orderDate'           |
+--------------+---------------+-----------------------+
| 'CR56'       | 'PA14'        | '2004-05-04 11:30:00' |
| 'CR62'       | 'PA14'        | '2004-05-04 14:00:00' |
| 'CR56'       | 'PG36'        | '2004-06-07 11:00:00' |
| 'CR56'       | 'PG4'         | '2004-04-14 12:05:00' |
| 'CR76'       | 'PG4'         | '2004-04-04 10:15:00' |
+--------------+---------------+-----------------------+

我正在尝试形成一个查询，以便在客户注册后的1、2和3个月内找到每个分支的订单数量。

比方说

+----------+------------+-----------------+
| 'months' | 'branchId' | 'numberOfOrder' |
+----------+------------+-----------------+
| 1        |  'B003'    |  2              |
| 2        |  'B004'    |  1              |
+----------+------------+-----------------+

我试着把桌子按月和日期分组，但我被困住了，无法继续前进。有人有什么想法帮我解围吗？

我开始做这样的事情，但我现在完全迷失了方向。

SELECT 
    COUNT(DISTINCT o.orderDate) AS 'count'
FROM
    Order o, Registration r
WHERE
    o.orderDate BETWEEN DATE('2001-01-01') AND DATE('2005-01-31')
GROUP BY YEAR(o.orderDate), MONTH(o.orderDate);

但这似乎离我想要达到的目标还很远。

Answer 1

我不完全确定您想要的结果是什么，但是通过这个查询，您可以在注册后3个月内获得每个分支的订单数量。

SELECT 
    reg.branchId,
    COUNT(reg.branchId) AS 'orderCount'
    FROM `order` AS ord INNER JOIN `registration` AS reg
    ON ord.customerId = reg.customerId
    WHERE reg.joiningDate BETWEEN reg.joiningDate AND DATE_ADD(reg.joiningDate, INTERVAL 3 MONTH)
    GROUP BY reg.branchId

结果

​

​

这就是你想做的吗？

Answer 2

您可以尝试下面的查询，我猜，根据orderDate和joiningDate中的差异计算蛾-

SELECT  abs(ceil(datediff(o.`orderDate`, r.`joiningDate`)/30)) months_join,r.branchId, COUNT(*) numberOfOrder
FROM registration r inner join `order` o ON r.`customerId` = o.`customerId`
GROUP BY YEAR(o.`orderDate`),  abs(ceil(datediff(o.`orderDate`, r.`joiningDate`)/30)),r.branchId