The stored procedure Developer_count is based on the incoming parameter SearchType decision is to use that query method, the other parameters contained in this stored procedure is {start time: Startime, End time: Endtime}
[HTML]View PlainCopy
- CREATE PROCEDURE Developer_count
- (
- SearchType int,
- StartTime varchar (64),
- EndTime varchar (64)
- )
- BEGIN
- /* Define Variable days */
- declare day_num int;
- If searchtype = 1 Then
- /* Data query this week */
- Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL 7 day) &L t;= Date (d.acct_old_time) GROUP by D.acct_old_time;
- End If;
- If searchtype = 2 Then
- /* Data query this month */
- Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL-day) & lt;= Date (d.acct_old_time) GROUP by D.acct_old_time;
- End If;
- If searchtype = 3 Then
- /* Data query for the last three months */
- Select count (d.acct_id), Date_format (D.acct_old_time, '%x year-%v week ') as weeks from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day) <= Date (d.acct_old_time) GROUP by weeks;
- End If;
- If searchtype = 4 Then
- /* Data statistics by Month */
- Select DateDiff (StartTime, endTime) into Day_num;
- If Day_num <=7 Then
- Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day_num Day) <= Date (d.acct_old_time) GROUP by D.acct_old_time;
- End If;
- If Day_num >7 && day_num <=
- Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day_num Day) <= Date (d.acct_old_time) GROUP by D.acct_old_time;
- End If;
- If Day_num >30 && day_num <=
- Select count (d.acct_id), Date_format (D.acct_old_time, '%x year-%v week ') as weeks from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day_num Day) <= Date (d.acct_old_time) GROUP by weeks;
- End If;
- End If;
- End
Calling a stored procedure method
Call Developer_count (1, ' 2016-06-07 ', ' 2016-06-16 '); Calling a stored procedure
MySQL-based stored procedures for data statistics by day, week, month statistics template