下面小編就為大家?guī)硪黄?a href="http://www.babyishan.com/?golink=aHR0cDovL3d3dy5waHAuY24vd2lraS8xMTYwLmh0bWw=" target="_blank" rel="noopener">mysql列轉(zhuǎn)行以及年月mysql實例。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
如下所示：
SELECT?count(DISTINCT(a.rect_id))?zcount,?a.job_dept,? DATE_format(submit_date,?'%Y-%m')?zsubmit_date? FROM? 表名?a? WHERE? a.statu?=?3? AND?a.rstatu?=?2? AND?a.job_dept?IN?('19',?'20',?'21')? GROUP?BY? a.job_dept,? DATE_FORMAT(submit_date,?'%Y-%m')
其中關鍵在于DATE_FORMAT(submit_date, ‘%Y-%m’)對時間年月進行了分組排序
SELECT?????? zsubmit_date,?????? MAX(CASE?WHEN?job_dept?=?'19'?THEN?zcount?ELSE?0?END?)?19zcount,?????? MAX(CASE?WHEN?job_dept?=?'20'?THEN?zcount?ELSE?0?END?)?20zcount,?????? MAX(CASE?WHEN?job_dept?=?'21'?THEN?zcount?ELSE?0?END?)?21zcount?? FROM???? (????? SELECT??????? count(DISTINCT(a.rect_id))?zcount,?a.job_dept,???????? DATE_FORMAT(submit_date,?'%Y-%m')?zsubmit_date????? FROM??????? 表名?a????? WHERE??????? a.statu?=?3??????? AND?a.rstatu?=?2??????? AND?a.job_dept?IN?('19',?'20',?'21')????? GROUP?BY????? a.job_dept,????? DATE_FORMAT(submit_date,?'%Y-%m')???? )?q??GROUP?BY???? zsubmit_date
? 版權聲明
文章版權歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
