如何用月份名称获取最近3个月的计数，如果没有该月份的记录，则需要用月份名称获取0

abithluo 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(296)

这个问题在这里已经有答案了：

mysql最近12个月的月销售额，包括无销售额的月份（2个答案）
每月获得记录，但如果当月没有记录，也可获得零（2个答案）
即使数据不存在，mysql也会选择逐月记录（2个答案）
mysql查询获取过去12个月的数据，按月份分组，包括零计数（1个答案）
去年关门了。
我想得到最近3个月的数据从当前日期，然后我会得到四个月的名字，因为我只有3个月的数据，但我没有一个月的数据在中间。

SELECT 
    count(v.visit_id) as count,
    MONTHNAME(v.updated) AS Month_name 

FROM patient_status as  p, visit_history_details as v  

WHERE v.visit_id = p.visit_id and v.hospital_code = 'id' 
    and p.doctor_id = '2' and v.updated >= now()-interval 3 month 
GROUP by Month_name

我的结果如下：

January  10 
December 12
October  10

但我想要这个结果：

January  10 
December 12
November 0 
October  10

请帮我解决这个问题

mysql

来源：https://stackoverflow.com/questions/53993687/how-to-get-the-counts-of-last-3-months-with-month-names-if-no-record-for-that-m

1条答案

按热度按时间

u0sqgete1#

对于月份，应该使用子查询来获取所需的所有月份

select monthname(my_date), ifnull(count, 0)
from (
      select str_to_date('2018-01-01', '%Y-%m-%d') as my_date 
      union 
      select str_to_date('2018-10-01', '%Y-%m-%d') 
      union 
      select str_to_date('2018-11-01', '%Y-%m-%d')
      union 
      select str_to_date('2018-12-01', '%Y-%m-%d')
    ) t  
left join  (
SELECT 
    count(v.visit_id) as count,
    MONTHNAME(v.updated) AS Month_name,
    MONTH(v.updated) as my_month
  FROM patient_status as  p 
  INNER JOIN  visit_history_details as v   
    ON v.visit_id = p.visit_id 

  WHERE v.hospital_code = 'id' 
  and p.doctor_id = '2' 
  and v.updated >= now()-interval 3 month 

  GROUP by Month_name

) r on r.my_month = t.my_month

您应该避免使用旧的隐式连接sintax并使用显式sintax
或者像萨尔曼a所建议的那样，找到一个更普遍的解决方案

select monthname(my_date), ifnull(count, 0)
from (
      select LAST_DAY(CURRENT_TIMESTAMP) + INTERVAL 1 DAY - INTERVAL 1 MONTH as my_date 
      union 
      select  LAST_DAY(CURRENT_TIMESTAMP) + INTERVAL 1 DAY - INTERVAL 2 MONTH
      union 
      select  LAST_DAY(CURRENT_TIMESTAMP) + INTERVAL 1 DAY - INTERVAL 3 MONTH
      union 
      select  LAST_DAY(CURRENT_TIMESTAMP) + INTERVAL 1 DAY - INTERVAL 4 MONTH
    ) t  
left join  (
SELECT 
    count(v.visit_id) as count,
    MONTHNAME(v.updated) AS Month_name,
    MONTH(v.updated) as my_month
  FROM patient_status as  p 
  INNER JOIN  visit_history_details as v   
    ON v.visit_id = p.visit_id 

  WHERE v.hospital_code = 'id' 
  and p.doctor_id = '2' 
  and v.updated >= now()-interval 3 month 

  WHERE v.visit_id = p.visit_id and v.hospital_code = 'id' 
      and p.doctor_id = '2' and v.updated >= now()-interval 3 month 
  GROUP by Month_name

赞(0）回复(0）举报 2021-06-17

我来回答

如何用月份名称获取最近3个月的计数，如果没有该月份的记录，则需要用月份名称获取0

1条答案

相关问题

热门标签

最新问答