MySQL查询30天前截止今天，每天打卡成功和打卡失败的人数

1. 引言

在日常工作或生活中，我们经常需要对数据进行查询和统计分析。MySQL是一种常用的关系型数据库管理系统，具有高效、稳定的特点。针对某些情况，我们需要查询并统计某段时间内的数据变化情况。本文将详细介绍如何使用MySQL进行30天前截止今天，每天打卡成功和打卡失败的人数的查询。

2. 数据库准备

为了演示方便，我们假设有一个名为attendance的数据库，其中包括以下两个表：

users表：记录用户信息，包括用户ID（id），用户名（name）等字段。

id	name
1	张三
2	李四
3	王五

checkin表：记录用户的打卡情况，包括用户ID（user_id），打卡时间（checkin_time）和打卡结果（result）等字段。其中，打卡结果为1表示成功，0表示失败。

user_id	checkin_time	result
1	2021-01-01 08:00	1
1	2021-01-01 18:00	1
2	2021-01-01 09:00	1
2	2021-01-01 18:00	0
3	2021-01-01 12:00	0
1	2021-01-02 08:00	1
1	2021-01-02 18:00	1
2	2021-01-02 09:00	1
2	2021-01-02 18:00	1
3	2021-01-02 12:00	0
…	…	…

3. 查询每天打卡成功和打卡失败的人数

我们需要查询30天前截止今天，每天打卡成功和打卡失败的人数。具体步骤如下：

3.1 连接数据库

首先，我们需要连接到MySQL数据库。

mysql -h localhost -u username -p

3.2 选择数据库

连接成功后，需要选择要使用的数据库。

use attendance;

3.3 查询每天打卡成功和打卡失败的人数

查询每天打卡成功和打卡失败的人数可以分为以下几个步骤：

3.3.1 获取当前日期和30天前的日期

在查询每天的数据之前，需要知道当前日期和30天前的日期。MySQL提供了内置函数CURDATE()用于获取当前日期，DATE_SUB()用于计算指定日期的前几天日期。

SET @end_date = CURDATE();
SET @start_date = DATE_SUB(@end_date, INTERVAL 30 DAY);

3.3.2 生成日期序列

根据起始日期和结束日期，我们需要生成一个包含所有日期的序列。MySQL中的GENERATE_SERIES函数可以用于生成指定范围内的连续整数序列，我们可以使用这个函数来生成日期序列。

SELECT DATE_ADD(@start_date, INTERVAL n DAY) AS date
FROM (
  SELECT n
  FROM (
    SELECT @row := @row + 1 AS n
    FROM users, (SELECT @row := 0) AS r
    LIMIT DATEDIFF(@end_date, @start_date) + 1
  ) AS x
) AS d

3.3.3 统计每天打卡成功和打卡失败的人数

生成日期序列后，我们可以根据日期序列，查询每天的打卡成功和打卡失败的人数。

SELECT
  d.date,
  COUNT(CASE WHEN c.result = 1 THEN 1 ELSE NULL END) AS success_count,
  COUNT(CASE WHEN c.result = 0 THEN 1 ELSE NULL END) AS failure_count
FROM
  (查询日期序列的SQL) AS d
LEFT JOIN
  checkin AS c ON d.date = DATE(c.checkin_time)
GROUP BY
  d.date

3.4 完整示例代码

下面是查询每天打卡成功和打卡失败的人数的完整示例代码：

-- 连接到MySQL数据库
mysql -h localhost -u username -p

-- 选择要使用的数据库
use attendance;

-- 获取当前日期和30天前的日期
SET @end_date = CURDATE();
SET @start_date = DATE_SUB(@end_date, INTERVAL 30 DAY);

-- 生成日期序列
SELECT DATE_ADD(@start_date, INTERVAL n DAY) AS date
FROM (
  SELECT n
  FROM (
    SELECT @row := @row + 1 AS n
    FROM users, (SELECT @row := 0) AS r
    LIMIT DATEDIFF(@end_date, @start_date) + 1
  ) AS x
) AS d
LEFT JOIN (
  -- 统计每天打卡成功和打卡失败的人数
  SELECT
    d.date,
    COUNT(CASE WHEN c.result = 1 THEN 1 ELSE NULL END) AS success_count,
    COUNT(CASE WHEN c.result = 0 THEN 1 ELSE NULL END) AS failure_count
  FROM
    (SELECT DATE_ADD(@start_date, INTERVAL n DAY) AS date
    FROM (
      SELECT n
      FROM (
        SELECT @row := @row + 1 AS n
        FROM users, (SELECT @row := 0) AS r
        LIMIT DATEDIFF(@end_date, @start_date) + 1
      ) AS x
    ) AS d) AS d
  LEFT JOIN
    checkin AS c ON d.date = DATE(c.checkin_time)
  GROUP BY
    d.date
) AS result ON d.date = result.date;