DATE_FORMAT (apply CREATE_TIME, 'Y - m - % d % %') AS a dateTime,
The sum (CASE WHEN apply. The STATUS='0' THEN '1' ELSE '0' END) s0,
The sum (CASE WHEN apply. The STATUS='1' THEN '1' ELSE '0' END) s1,
The sum (CASE WHEN apply. The STATUS='2' THEN '1' ELSE '0' END) s2,
The sum (CASE WHEN apply. The STATUS='4' THEN '1' ELSE '0' END) s3
The FROM
USER_APPLY apply
WHERE
Yearweek (date_format (apply CREATE_TIME, 'Y - m - % d % %))=yearweek (now ())
GROUP BY a dateTime
The ORDER BY a dateTime ASC
SQL as above, the statistics of the query is in this week, the number of the state of the status of 0,1,2,4 each have how many, the results are as follows:
One thing need to improve is that one day, when no data is all statistics is 0, the default not show the date, advice on how to optimize the great god, not their academic home, don't come out,
CodePudding user response:
Select count (*) as people, stutas FROM USER_APPLY where yearweek (date_format (apply CREATE_TIME, 'Y - m - % d % %))=yearweek (now ()) group by stutas