mysql - 查询月份帮助

Question

嘿，我需要一些有用的提示/建议来解决我的问题。我有一个包含“注册”表的数据库。此表的日期格式如下:

2010-04-03 00:00:00

现在假设我在这个数据库中有 10 条记录:

2010-04-03 00:00:00
2010-01-01 00:00:00
2010-06-22 00:00:00
2010-02-08 00:00:00
2010-02-05 00:00:00
2010-03-08 00:00:00
2010-09-29 00:00:00
2010-11-16 00:00:00
2010-04-09 00:00:00
2010-05-21 00:00:00

我想获得每个月的总注册量......所以按照上面的例子:

Jan = 1
Feb = 2
Mar = 1
Apr = 2
May = 1
Jun = 1
Jul = 0
Aug = 0
Sep = 1
Oct = 0
Nov = 1
Dec = 0

现在我怎样才能使用查询来做到这一点，但不必使用像这样的查询:

 WHERE left(date, 7) = '2010-01'

并继续这样做 12 次？我希望它是一个单一的查询调用，只是让它把几个月的访问放到一个数组中，如下所示:

 do until EOF
   theMonthArray[0] = "total for jan"
   theMonthArray[1] = "total for feb"
   theMonthArray[2] = "total for mar"
   theMonthArray[3] = "total for apr"
   ...etc
 loop

除了我发布的每个月调用 12 个查询的示例之外，我想不出其他方法来做到这一点。

这是我目前的查询。同样，这只会填充一个月，而我试图一次填充所有 12 个月。

 SELECT count(idNumber) as numVisits, theAccount, signUpDate, theActive
 from userinfo
 WHERE theActive = 'YES'
 AND idNumber = '0203'
 AND theAccount = 'SUB'
 AND left(signUpDate, 7) = '2010-04'
 GROUP BY idNumber
 ORDER BY numVisits;

上面的示例查询输出如下:

 numVisits | theAccount | signUpDate          | theActive
 2           SUB          2010-04-16 00:00:00   YES

这是正确的，因为我在 4 月份有 2 条记录。

但同样，我试图一次完成所有 12 个月(在单个查询中)，因此与执行 12 个不同的查询相比，我不会对数据库服务器征税太多...

更新我正在寻找类似的方法:

 if NOT rst.EOF
    if left(rst("signUpDate"), 7) = "2010-01" then
        theMonthArray[0] = rst("numVisits")
    end if        

    if left(rst("signUpDate"), 7) = "2010-02" then
        theMonthArray[1] = rst("numVisits")
    end if
    etc etc....
 end if

任何帮助都会很棒! :)

大卫

Answer 1

您需要创建一个包含所有月份的派生表并将其连接到您的查询中，以便您获得零计数而不是丢失那些没有条目的月份的行。以下查询有点长，但应该能满足您的要求:

SELECT
    T1.`month`,
    COALESCE(numVisits, 0) AS numVisits
FROM (
    SELECT 1 AS month
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
    UNION ALL
    SELECT 6
    UNION ALL
    SELECT 7
    UNION ALL
    SELECT 8
    UNION ALL
    SELECT 9
    UNION ALL
    SELECT 10
    UNION ALL
    SELECT 11
    UNION ALL
    SELECT 12
) AS T1
LEFT JOIN (
    SELECT
        MONTH(signUpDate) AS `month`,
        COUNT(*) AS numVisits
    FROM userinfo
    WHERE theActive = 'YES'
    AND idNumber = '0203'
    AND theAccount = 'SUB'
    AND YEAR(signUpDate) = 2010
    GROUP BY MONTH(signUpDate)
) AS T2 ON T1.`month` = T2.`month`