mysql - 从时间戳列中选择特定的月份和年份记录mysql

Question

我想做的是从特定的月份和年份中选择和计算患者的诊断。我已经选择并计算了诊断我的问题是如何在特定的月份和年份中选择它。

我的查询 SELECT diagnosis, count(diagnosis) as total from patient_records where deleted_at is null group by diagnosis

表结构 Patient_records

#   Name    Type    Collation   Attributes  Null    

1   idPrimary   int(10)     UNSIGNED    No

2   diagnosis   varchar(191)    utf8_unicode_ci     

3   remarks text    utf8_unicode_ci     Yes NULL             

4   created_at  timestamp           Yes NULL             

5   updated_at  timestamp           Yes NULL             

6   deleted_at  timestamp           Yes NULL             

7   patient_id  int(10)     UNSIGNED    Yes NULL             

请帮助我，我是初学者，我心中的查询是这样的

 SELECT diagnosis, count(diagnosis) as total from patient_records where 
 deleted_at is null and updated_at = month(5)&year(2019) group by 
 diagnosis

标签： mysql

Answer 1

SELECT 
  diagnosis, 
  count(diagnosis) as total,
  DATE_FORMAT(created_at, '%m') As month, 
  YEAR(created_at) as Year 
from patient_records 
where deleted_at is null 
group by diagnosis