1. 查询每天的数据：统计每天的用例总数，by day

# 查询每天的数据
SELECT
    COUNT(1) AS countNumber,
    DATE_FORMAT(create_time,'%Y-%m-%d') AS dateTime
FROM project_case
GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d');

执行结果：

image.png

2. 查询每天的数据：统计每人每天的用例总数（仅展示有数据的日期），by day , user

SELECT
    COUNT(1) AS countNumber,
    DATE_FORMAT(create_time,'%Y-%m-%d') AS dateTime,
    create_by
FROM project_case
GROUP BY dateTime, create_by ORDER BY dateTime ASC;

执行结果：

image.png

3. 查询每周的数据：by week

# 查询每周的数据
SELECT 
    DATE_FORMAT(create_time,'%Y-%u') AS week,
    DATE_FORMAT(DATE_SUB(create_time,INTERVAL DAYOFWEEK(create_time) - 2 DAY),'%Y-%m-%d') AS createDate,
    COUNT(*) AS countNumber
FROM project_case
GROUP BY week ORDER BY week ASC;

执行结果：

image.png

第一列的周数据，表示每一年的第几周，从第一周开始计算起。

4. 查询每周的数据：by week, user

# 查询每周的数据:by WEEK, user
SELECT 
    DATE_FORMAT(create_time,'%Y-%u') AS week,
    DATE_FORMAT(DATE_SUB(create_time,INTERVAL DAYOFWEEK(create_time) - 2 DAY),'%Y-%m-%d') AS createDate,
    COUNT(*) AS countNumber,
    create_by
FROM project_case
GROUP BY week, create_by ORDER BY week ASC;

执行结果：

image.png

5. 查询每月的数据：by month

# 查询每月的数据:by month
SELECT 
    DATE_FORMAT(create_time,'%Y-%m') AS month,
    DATE_FORMAT(create_time,'%Y-%m-01') AS createDate,
    COUNT(*) AS countNumber
FROM project_case
GROUP BY month ORDER BY month ASC;

执行结果：

image.png

6. 查询每年的数据

# 查询每年的数据
SELECT
    COUNT(1) AS countNumber,
    YEAR(create_time) AS dateTime
FROM project_case
GROUP BY dateTime;

执行结果：

image.png