MySQL 5 上的 MONTHNAME() 问题 [英] Problems with MONTHNAME() on MySQL 5

问题描述

我正在尝试对我们的数据库生成报告.我们想知道每个行业每月的新注册量.我写了这个查询:

I'm trying to run a report on our database. We want to know the new registrations per industry per month. I've written this query:

SELECT 
  COUNT(j.jobseeker_id) as new_registrations, 
  i.description as industry_name,
  MONTHNAME(j.created_at)
FROM
  tb_jobseeker as j, tb_industry as i
WHERE 
  YEAR(j.created_at) = 2009 
AND 
  i.industry_id = j.industry_id 
GROUP BY 
  i.description, MONTHNAME(j.created_at)
HAVING
  MONTHNAME(j.created_at) =  MONTHNAME(NOW());

当我运行这个查询时，我得到一个空的结果集.但是，如果我运行以下命令:

When I run this query, I get an empty result set. However, if I run the following:

SELECT 
  COUNT(j.seeker_id) as new_registrations, 
  i.description as industry_name,
  MONTHNAME(j.created_at)
FROM
  tb_seeker as j, tb_industry as i
WHERE 
  YEAR(j.created_at) = 2009 
AND 
  i.industry_id = j.industry_id 
GROUP BY 
  i.description, MONTHNAME(j.created_at)
HAVING
  MONTHNAME(j.created_at) =  'June';

它返回我正在寻找的结果.

It returns the results I'm looking for.

请问有什么帮助吗?我被难住了.

Any help please? I'm stumped.

更新:查询将在上个月的每个月底或下月初运行.所以，我们现在是六月，但它需要运行到五月.希望这是有道理的.

Update: the query will be run at the end of every month or start of the next for the past month. So, we're now in June, but it needs to run for May. Hope that makes sense.

推荐答案

Errr.... it's not May O_o

Errr.... it's not May O_o

如果你这样写，你的查询会更有效率:

Your query will be much more efficient if you write it like this:

SELECT 
  COUNT(j.jobseeker_id) as new_registrations, 
  i.description as industry_name,
  MONTHNAME(j.created_at)
FROM
  tb_jobseeker as j, tb_industry as i
WHERE 
  j.created_at BETWEEN '2009-05-01' AND '2009-05-31'
AND 
  i.industry_id = j.industry_id 
GROUP BY 
  i.description, MONTH(j.created_at)

只有在绝对必须的情况下才应该使用 HAVING.

You should only use HAVING if you absolutely HAVE to.

如果可以按 i.id 而不是 i.description 分组会更好，但这取决于 i.description 是否唯一.

It would be even better if you could group by i.id instead of i.description, but that depends on whether i.description is unique.

这篇关于MySQL 5 上的 MONTHNAME() 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！