sql查询选定时间内容的用户总数和新增人数

sql 接口 theDate为选定日期的前一天，days 共几天

List statEverydayTotalUser(@Param("theDate") String theDate,@Param("days") Long days);


sql语句

SELECT
d.date,
d.num,
@total := @total + d.num AS num
FROM
(
SELECT
c.date AS date,
Ifnull( newadd, 0 ) AS num
FROM
(
SELECT
@cdate := date_add( @cdate, INTERVAL 1 DAY ) date
FROM
( SELECT @cdate := '2021-5-24' FROM USER LIMIT 2 ) AS a
) AS c
LEFT JOIN (
SELECT
date( create_time ) AS rat_time,
count( id ) AS newadd
FROM
city_challenge.user

GROUP BY
rat_time
) AS temp ON c.date = temp.rat_time
) AS d,
(
SELECT
@total := ( SELECT count( id ) FROM USER WHERE date( create_time ) <= '2021-5-24' )) AS T1


时间为选定日期的前一天(获取指定日期的前一天)

// 指定日期的前几天
public static String dateTheDelay( Date date ,int day) {
// Date date = new Date();// 取时间
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.add(calendar.DATE, day);
date = calendar.getTime();
String delayThreeDay = DateUtils.dateToString(date, "yyyy-MM-dd");
return delayThreeDay;
}


天数

public static long getDateDays(String starTime ,String endTime) throws ParseException {
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date starDate = dateFormat.parse(starTime);
Date endDate = dateFormat.parse(endTime);
long nd = 1000 * 24 * 60 * 60;
long nh = 1000 * 60 * 60;
long nm = 1000 * 60;
long ns = 1000;
long diff = endDate.getTime() - starDate.getTime();
long sec = diff / nd;
return sec;
}