mysql选择每个月的记录数 [英] mysql select the count of records for every month

问题描述

我需要在mysql中制作一个查询，该查询将返回12行(每月一行)，该行选择月份名称以及给定月份的记录数.我有两个表，months_tbl和events_tbl. events_tbl中的每个记录都有一个datetime列和一个company_id列.

I need to craft a query in mysql that will return 12 rows (one row for each month) that selects the month name, and the count of records for the given month. I have two tables, a months_tbl and events_tbl. Each record in the events_tbl has a datetime column and a company_id column.

我目前正在做类似的事情(注意我还没有WHERE company_id子句):

I'm currently doing something like (notice I don't have the WHERE company_id clause yet):

SELECT months_tbl.month, COUNT( events_tbl.event_id ) cnt
FROM months_tbl
LEFT JOIN events_tbl ON months_tbl.month_id = MONTH( events_tbl.appt_date_time )
GROUP BY months_tbl.month
ORDER BY months_tbl.month_id ASC;

这将返回我期望的结果(选择了12行，具有当月的事件计数，如果没有，则为零):

This returns something like what I'm expecting (12 rows selected, with a count of events for the month, or zero if there were none):

**month**    **cnt**
January      0
February     0
March        0
April        0
May          0
June         0
July         0
August       88
September    99
October      120
November     0
December     9

但是，无论公司如何，它都会返回所有记录.我需要确保查询被这样过滤，我添加了where子句:

however it's returning all records regardless of company. I need to make sure the query is filtered by so, I added the where clause:

SELECT months_tbl.month, COUNT( events_tbl.appt_id ) cnt
FROM months_tbl
LEFT JOIN events_tbl ON months_tbl.month_id = MONTH( events_tbl.appt_date_time ) 
WHERE events_tbl.company_id = 1 
GROUP BY months_tbl.month
ORDER BY months_tbl.month_id ASC;

当我添加where子句时，我的结果变为:

When I add the where clause my results become:

**month**    **cnt**
August       88
September    99
October      120
December     9

有什么想法为什么我在添加where子句时会丢失所有其他月份的记录，即使我使用的是左联接?

Any ideas why I'm losing all the other months records when I add the where clause, even though I'm using a left join?

推荐答案

您正在使用LEFT JOIN，但是在where语句中将其设为正常" JOIN

You are using a LEFT JOIN, but in your where statement your making it a 'normal' JOIN

尝试这样写:

SELECT months_tbl.month, COUNT( events_tbl.appt_id ) cnt
FROM months_tbl
  LEFT JOIN events_tbl ON (months_tbl.month_id = MONTH(events_tbl.appt_date_time) 
    AND events_tbl.company_id = 1
  )
GROUP BY months_tbl.month
ORDER BY months_tbl.month_id ASC;

请注意AND events_tbl.company_id = 1在LEFT JOIN

这篇关于mysql选择每个月的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！