Sample code sharing for mysql column switching and year and month grouping-Mysql Tutorial-php.cn

Sample code sharing for mysql column switching and year and month grouping

黄舟

Release： 2017-03-25 13:27:19

Original

1029 people have browsed it

The following editor will bring you an example of mysqlcolumn transfer and year and monthgrouping. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

As shown below:

SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept, 

DATE_FORMAT(submit_date, &#39;%Y-%m&#39;) zsubmit_date 

FROM 

表名 a 

WHERE 

a.statu = 3 

AND a.rstatu = 2 

AND a.job_dept IN (&#39;19&#39;, &#39;20&#39;, &#39;21&#39;) 

GROUP BY 

a.job_dept, 

DATE_FORMAT(submit_date, &#39;%Y-%m&#39;)

Copy after login

The key is DATE_FORMAT(submit_date, '%Y-%m ')Group and sort the time, year and month

SELECT      
zsubmit_date,      
MAX(CASE WHEN job_dept = &#39;19&#39; THEN zcount ELSE 0 END ) 19zcount,      
MAX(CASE WHEN job_dept = &#39;20&#39; THEN zcount ELSE 0 END ) 20zcount,      
MAX(CASE WHEN job_dept = &#39;21&#39; THEN zcount ELSE 0 END ) 21zcount  

FROM    
(     
SELECT       
count(DISTINCT(a.rect_id)) zcount, a.job_dept,        
DATE_FORMAT(submit_date, &#39;%Y-%m&#39;) zsubmit_date     
FROM       
表名 a     
WHERE       
a.statu = 3       
AND a.rstatu = 2       
AND a.job_dept IN (&#39;19&#39;, &#39;20&#39;, &#39;21&#39;)     
GROUP BY     
a.job_dept,     
DATE_FORMAT(submit_date, &#39;%Y-%m&#39;)    
) q  GROUP BY    
zsubmit_date

Copy after login

The above is the detailed content of Sample code sharing for mysql column switching and year and month grouping. For more information, please follow other related articles on the PHP Chinese website!