MySQL 查询 - 每个月过去 12 个月记录的返回值 [英] MySQL Query - return value for past 12 months record for each month

问题描述

我试图获得超过 12 个月的预订日期计数，但对于没有预订的月份，我需要得到 Null 或 0.我在下面的查询会跳过没有预订的月份，

I am trying to get past 12 months booking date count, but for the months with no booking, i need to get Null or 0. My query below skips the months with no booking,

SELECT bs.date,CONCAT(MONTHNAME(bs.date),' ', YEAR(bs.date)) as 'Month',count(*) AS Bookings, CONCAT(us.firstName,' ', us.lastName) as 'Name' from bookings bs RIGHT JOIN users us ON bs.clientID = us.recNo
        WHERE bs.managerID = 6 AND bs.clientID = 1900
        AND date > DATE_SUB(CURDATE(), INTERVAL 11 MONTH) AND date <= CURDATE() GROUP BY YEAR(date), month(date) 

有人可以帮忙吗?

推荐答案

您可以通过硬编码 12 个月并与 users CROSS JOIN 然后使用 bookings LEFT JOIN 来实现代码>(我听说其他人提到 LEFT JOIN 比 RIGHT JOIN 快得多).所以在下面查询

You can do it by hardcoding the 12 months in there and CROSS JOIN with users then LEFT JOIN with bookings (I have heard others mentioned that LEFT JOIN is way faster than RIGHT JOIN). So query below

SELECT bs.date,
      CONCAT(MONTHNAME(hardcoded.date),' ', YEAR(hardcoded.date)) as 'Month',
       count(bs.date) AS Bookings,
      CONCAT(us.firstName,' ', us.lastName) as 'Name' 
from 
(SELECT CURDATE() as date UNION
 SELECT CURDATE()-INTERVAL 1 MONTH UNION
 SELECT CURDATE()-INTERVAL 2 MONTH UNION
 SELECT CURDATE()-INTERVAL 3 MONTH UNION
 SELECT CURDATE()-INTERVAL 4 MONTH UNION
 SELECT CURDATE()-INTERVAL 5 MONTH UNION
 SELECT CURDATE()-INTERVAL 6 MONTH UNION
 SELECT CURDATE()-INTERVAL 7 MONTH UNION
 SELECT CURDATE()-INTERVAL 8 MONTH UNION
 SELECT CURDATE()-INTERVAL 9 MONTH UNION
 SELECT CURDATE()-INTERVAL 10 MONTH UNION
 SELECT CURDATE()-INTERVAL 11 MONTH
 )as hardcoded
CROSS JOIN users us
LEFT JOIN bookings bs ON (MONTH(bs.date) = MONTH(hardcoded.date)
                       AND YEAR(bs.date) = YEAR(hardcoded.date))
                      AND bs.clientID = us.recNo
WHERE ((bs.managerID = 6 AND bs.clientID = 1900)
        OR
      (bs.managerID IS NULL AND bs.clientID IS NULL))
AND hardcoded.date > DATE_SUB(CURDATE(), INTERVAL 11 MONTH) AND hardcoded.date <= CURDATE() 
GROUP BY Month,Name
ORDER BY hardcoded.date

参见 (sqlFiddle)
注意:因为有一个 WHERE 条件来检查 managerID 和 clientID 以过滤出我们想要的结果，但我们也想返回具有空值的行，这意味着我们的硬编码月份没有预订.这就是为什么有 OR (bs.managerID IS NULL AND bs.clientID IS NULL)
此外，当前的 GROUP BY 是非标准的，因为它没有对 select bs.date 的第一个字段进行分组，所以现在如果您在一个月内有 2 个或更多的预订，它只会返回一个日期而不是知道哪一个看起来很奇怪，您可能想要选择 hardcoded.date 而不是 GROUP BY，以便您拥有 ANSI-Standard 正确的 GROUP BY.或者更好地使用像 MIN() 或 MAX() 到 MIN(bs.date) 或 MAX(bs.date) 这样的聚合函数作为选择中的第一个字段，以确保它始终返回第一个预订日期或最后一个预订如果该月有 2 个或更多预订，则为每个月指定日期，这样您就不会将其列在 GROUP BY 中.

see this in (sqlFiddle)
Note: because have a WHERE condition to check on managerID and clientID to filter out results we want but we also want to return rows that have nulls meaning it's our hardcoded months that have no bookings. that's why there's the OR (bs.managerID IS NULL AND bs.clientID IS NULL)
Also the current GROUP BY is NON-Standard as it is not grouping the first field of the select bs.date so right now if you have 2 or more bookings in one month, it just returns a date not knowing which one which seems odd, you might want to select hardcoded.date instead and GROUP BY that too so that you have a ANSI-Standard proper GROUP BY. Or better yet use an aggregate function like MIN() or MAX() to MIN(bs.date) or MAX(bs.date) for the first field in the select to make sure it always returns the first booking date or the last booking date for a each month if that month has 2 or more bookings, so that you don't don't list it in the GROUP BY.

我不知道 CROSS JOIN 是否会杀死您的应用程序，但您可以试试这个它没有 CROSS JOIN 但保留您的 RIGHT JOIN

I don't know if the CROSS JOIN is killing your app or not but you can try this one it's without the CROSS JOIN but preserving your RIGHT JOIN

SELECT bs.date,
 CONCAT(MONTHNAME(hardcoded.date),' ', YEAR(hardcoded.date)) as 'Month',
 count(bs.date) AS Bookings,
 CONCAT(us.firstName,' ', us.lastName) as 'Name' 
from 
(SELECT CURDATE() as date UNION
 SELECT CURDATE()-INTERVAL 1 MONTH UNION
 SELECT CURDATE()-INTERVAL 2 MONTH UNION
 SELECT CURDATE()-INTERVAL 3 MONTH UNION
 SELECT CURDATE()-INTERVAL 4 MONTH UNION
 SELECT CURDATE()-INTERVAL 5 MONTH UNION
 SELECT CURDATE()-INTERVAL 6 MONTH UNION
 SELECT CURDATE()-INTERVAL 7 MONTH UNION
 SELECT CURDATE()-INTERVAL 8 MONTH UNION
 SELECT CURDATE()-INTERVAL 9 MONTH UNION
 SELECT CURDATE()-INTERVAL 10 MONTH UNION
 SELECT CURDATE()-INTERVAL 11 MONTH
 )as hardcoded
LEFT JOIN bookings bs ON (MONTH(bs.date) = MONTH(hardcoded.date)
 AND YEAR(bs.date) = YEAR(hardcoded.date))
RIGHT JOIN users us ON (bs.clientID = us.recNo OR bs.clientID IS NULL)
WHERE ((bs.managerID = 6 AND bs.clientID = 1900)
 OR
 (bs.managerID IS NULL AND bs.clientID IS NULL))
AND hardcoded.date > DATE_SUB(CURDATE(), INTERVAL 11 MONTH) AND hardcoded.date <= CURDATE() 
GROUP BY Month,Name
ORDER BY hardcoded.date

在加入之前尝试缩小记录范围

Trying to narrow down records before JOINING

SELECT MIN(bs.date) as FirstBookingDate,
 CONCAT(MONTHNAME(hardcoded.date),' ', YEAR(hardcoded.date)) as 'Month',
 count(bs.date) AS Bookings,
 CONCAT(us.firstName,' ', us.lastName) as 'Name' 
from 
(SELECT CURDATE() as date UNION
 SELECT CURDATE()-INTERVAL 1 MONTH UNION
 SELECT CURDATE()-INTERVAL 2 MONTH UNION
 SELECT CURDATE()-INTERVAL 3 MONTH UNION
 SELECT CURDATE()-INTERVAL 4 MONTH UNION
 SELECT CURDATE()-INTERVAL 5 MONTH UNION
 SELECT CURDATE()-INTERVAL 6 MONTH UNION
 SELECT CURDATE()-INTERVAL 7 MONTH UNION
 SELECT CURDATE()-INTERVAL 8 MONTH UNION
 SELECT CURDATE()-INTERVAL 9 MONTH UNION
 SELECT CURDATE()-INTERVAL 10 MONTH UNION
 SELECT CURDATE()-INTERVAL 11 MONTH
 )as hardcoded
CROSS JOIN (SELECT * FROM users WHERE recNo = 1900) us
LEFT JOIN (SELECT * FROM bookings WHERE managerID = 6 AND clientID = 1900
           AND date BETWEEN CURDATE()-INTERVAL 11 MONTH AND CURDATE()) bs 
 ON (MONTH(bs.date) = MONTH(hardcoded.date)
 AND YEAR(bs.date) = YEAR(hardcoded.date))
GROUP BY Month,Name
ORDER BY hardcoded.date

这篇关于MySQL 查询 - 每个月过去 12 个月记录的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！