Use SQL to count the daily total of a table and the incremental mysql database

For example: a user table has a user_id self-incrementing primary key and a create_time user creation time

Business requirements allow you to count the total number of users per day and how many users have increased compared to the previous day

As shown in figure 2.

First of all, check out the total amount of each day. The time in the table is generally of date_time type, so use a function.

SELECT DATE_FORMAT (create_time,'%Y-%m-%d') dd,COUNT (*) num FROM USER GROUP BY DATE_FORMAT (create_time,'%y-%m-%d')

Use the previous SQL as a statistical table to calculate the incremental SQL. You can try it for yourself.

SELECT a.dd.nummam (lt.num) AS cum FROM (SELECT DATE_FORMAT (create_time,'%Y-%m-%d') dd,COUNT (*) num FROM USER GROUP BY DATE_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) '% ymi% mmi% d') ltON a.dd > = lt.dd GROUP BY dd

(table 1) (Table 2)