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
