mysql - 每月约会，12 月不见了？

Question

SELECT month(dateofappointment), COUNT(*) 'NumberOfAppointments'
    FROM appointment 
    WHERE YEAR(dateofappointment) = '2016'  
    GROUP BY  MONTH(dateofappointment) 

这显示了所有月份，但 12 月不存在，因为那一年没有任何约会。如何将 12 月显示为 0？

Answer 1

为了解决这些类型的查询，将它们表达为一系列需求通常会有所帮助，这可以使其更容易解决。

当结果未按预期出现时，请在确定新要求时更新您的要求声明，然后重试:

据我所知，您现在有 2 个要求:

1. 返回 2016 年每个月的单行
2. 每行显示相应月份的预约计数

好吧，这很冗长，但是您会看到查询中缺少的是一个定义“2016 年每个月的 1 行”的语句，因此您需要首先手动或通过递归构建该记录集.

MySQL does not currently support recursive Common Table Expressions, this is a trivial concept in many other RDBMSs

但是如果MySQL不支持递归，我们有什么选择呢？以下是一些其他尝试:

• МуSQL Get a list of dates in month, year
• How to generate a dynamic sequence table in MySQL?
• generate an integer sequence in MySQL

这可能听起来有点像黑客，但您可以使用数据库中任何超过 12 行且具有自动递增字段的表，哦，并且种子从 1(或更低)开始。忘记这是否正确或错误，它会起作用:

SELECT Id 
FROM LogEvent -- An arbitrary table that I know has records starting from 1
WHERE Id BETWEEN 1 AND 12

所以这是 hacky，但我们可以实现行计数函数，这样我们就可以使用任何具有 12 行或更多行的表，无论 id 或种子，从以下位置窃取: MySQL get row number on select -Answer by Mike Cialowicz

SET @rank=0;
SELECT @rank:=@rank+1 AS rank
FROM orders
WHERE rank <= 12

现在我们可以将该结果集中缺失的行合并到原始查询中，或者使用连接运算符。使用联合的第一个解决方案。

It is common to use UNION ALL to inject missing rows to a recordset because it separates the expected result query from the exceptional or default results. Sometimes this syntax makes it easier to interpret the expected operation

SET @rank = 0;

SELECT month(dateofappointment) as Month, COUNT(*) 'NumberOfAppointments'
FROM appointment 
WHERE YEAR(dateofappointment) = '2016'  
GROUP BY  MONTH(dateofappointment) 

UNION ALL

SELECT rank, 0
FROM (
    SELECT @rank:=@rank+1 AS rank
    FROM rows
    WHERE @rank < 12
) months
WHERE NOT EXISTS (SELECT dateofappointment 
                  FROM appointment
                  WHERE YEAR(dateofappointment) = '2016' AND MONTH(dateofappointment) = months.rank)
ORDER BY Month

但这会导致一个丑陋的查询。您还可以通过对约会计数进行左连接来连接月份查询，但这里的意图更难识别。

SET @rank = 0;

SELECT months.rank, COUNT(appointment.dateofappointment) 
FROM (
    SELECT @rank:=@rank+1 AS rank
    FROM rows
    WHERE @rank < 12
) months
LEFT OUTER JOIN appointment ON months.rank = Month(appointment.dateofappointment) AND YEAR(dateofappointment) = '2016'
GROUP BY months.rank

I have saved these queries into a SqlFiddle so you can see the results : http://sqlfiddle.com/#!9/99d485/4

As I pointed out above, this is trivial in MS SQL and Oracle RDBMS, where we can generate sequences of values dynamically through recursive Common Table Expressions (CTEs) For the players at home here is an implementation in MS SQL Server 2014. The example is a little more evolved, using a from and to date to filter the results dynamically

-- Dynamic MS SQL Example using recursive CTE
DECLARE @FromDate Date = '2016-01-01'
DECLARE @ToDate Date = '2016-12-31'
;
WITH Months(Year, Month, Date) AS
(
    SELECT Year(@FromDate), Month(@FromDate), @FromDate
    UNION ALL
    SELECT Year(NextMonth.Date), Month(NextMonth.Date), NextMonth.Date
    FROM Months
    CROSS APPLY (SELECT DateAdd(m, 1, Date) Date) NextMonth
    WHERE NextMonth.Date < @ToDate
)
SELECT Months.Year, Months.Month, COUNT(*) as 'NumberOfAppointments'
FROM Months
LEFT OUTER JOIN appointment ON Year(dateofappointment) = Months.Year AND Month(dateofappointment) = Months.Month
GROUP BY Months.Year, Months.Month