Use SQL to count the total and incremental MySQL database for a table per day

Example: A user table has a user_id self-increment primary key and Create_time

Business requirements allow you to count the total number of users per day and the amount of user increase compared to the preceding

Table two this.

First of all, the total number of daily to detect the time in the table is generally date_time type, so use a function

SELECT date_format (create_time, '%y-%m-%d ') dd,count (*) num from the USER GROUP by Date_format (create_time, '%y-%m-%d ')

Use the previous SQL as a table of statistics to calculate the increment on SQL. You can try it yourself.

Select A.dd,a.num,sum (lt.num) as Cum from (select Date_format (create_time, '%y-%m-%d ') dd,count (*) num from USER GROUP by D Ate_format (create_time, '%y-%m-%d ')) Ajoin (SELECT date_format (create_time, '%y-%m-%d ') dd,count (*) num from USER GROUP by Date_format (Create_time, '%y-%m-%d ')) Lton a.dd >= lt.dd GROUP by DD

650) this.width=650; "Src=" Https://s3.51cto.com/oss/201711/08/dd7ab24069748878d639fd7a69107f74.png-wh_500x0-wm_3 -wmp_4-s_1693574468.png "title=" Image.png "alt=" Dd7ab24069748878d639fd7a69107f74.png-wh_ "/> (Table i) 650) this.width =650; "Src=" Https://s1.51cto.com/oss/201711/08/af70e661afde991f19cdc09787650067.png-wh_500x0-wm_3-wmp_4-s_ 424625856.png "title=" Image.png "alt=" Af70e661afde991f19cdc09787650067.png-wh_ "/> (Table II)

This article is from the "bolted snail" blog, please make sure to keep this source http://woniu123.blog.51cto.com/11993907/1979970

Use SQL to count the total and incremental MySQL database for a table per day