迹忆客 专注技术分享

本篇文章使用 GROUP BY 子句、COUNT() 和 DATE() 函数仅在 MySQL 中按 DATE 对 DATETIME 类型列进行分组。

在 MySQL 中使用 GROUP BY、COUNT() 和 DATE() 仅按 DATE 对 DATETIME 列进行分组

在继续之前，请记住 DATETIME 值看起来像 YYYY-MM-DD hh:mm:ss 而 DATE 值的格式为 YYYY-MM-DD。 我们只想在列的数据类型为 DATETIME 时按 DATE 对数据进行分组。

我们将创建两个名为 students 和 student_attendance 的表。 students 表包含每个学生的基本详细信息，而 student_attendance 表包含 STUDENT_ID 和 ATTENDANCE 作为属性（列）。

出于学习目的，我们还可以使用下面的查询创建这两个表。

示例代码（创建表）：

# Create a `students` table
CREATE TABLE students(
   ID INT NOT NULL PRIMARY KEY,
    FIRSTNAME VARCHAR(45) NOT NULL,
    LASTNAME VARCHAR(45) NOT NULL,
    GENDER VARCHAR(10) NOT NULL
);

# Create a `student_attendance` table
CREATE TABLE student_attendance(
    STUDENT_ID INT NOT NULL,
    ATTENDANCE DATETIME NOT NULL,
    FOREIGN KEY (STUDENT_ID) REFERENCES students(ID)
);

示例代码（将数据插入表中）：

# Insert data into the `students` table
INSERT INTO students (ID, FIRSTNAME, LASTNAME, GENDER)
VALUES
(1, 'Mehvish', 'Ashiq', 'Female'),
(2, 'Thomas', 'Christopher', 'Male'),
(3, 'John', 'Jackson', 'Male');

# Insert data into the `stduent_attendance` table
INSERT INTO student_attendance (STUDENT_ID, ATTENDANCE)
VALUES
(1, '2022-05-02 08:15:10'),
(2, '2022-05-02 08:15:10'),
(3, '2022-05-02 08:15:10'),
(1, '2022-05-03 08:15:10'),
(2, '2022-05-03 08:15:10'),
(3, '2022-05-03 08:15:10'),
(1, '2022-05-04 08:15:10'),
(2, '2022-05-04 08:15:10'),
(3, '2022-05-04 08:15:10'),
(1, '2022-05-05 08:15:10'),
(2, '2022-05-05 08:15:10'),
(3, '2022-05-05 08:15:10'),
(1, '2022-05-06 08:15:10'),
(2, '2022-05-06 08:15:10'),
(3, '2022-05-06 08:15:10');

示例代码（显示数据）：

SELECT * from students;
SELECT * from student_attendance;

输出 (students 表):

+----+-----------+-------------+--------+
| ID | FIRSTNAME | LASTNAME    | GENDER |
+----+-----------+-------------+--------+
|  1 | Mehvish   | Ashiq       | Female |
|  2 | Thomas    | Christopher | Male   |
|  3 | John      | Jackson     | Male   |
+----+-----------+-------------+--------+
3 rows in set (0.00 sec)

输出（对于 student_attendance 表）：

+------------+---------------------+
| STUDENT_ID | ATTENDANCE          |
+------------+---------------------+
|          1 | 2022-05-02 08:15:10 |
|          2 | 2022-05-02 08:15:10 |
|          3 | 2022-05-02 08:15:10 |
|          1 | 2022-05-03 08:15:10 |
|          2 | 2022-05-03 08:15:10 |
|          3 | 2022-05-03 08:15:10 |
|          1 | 2022-05-04 08:15:10 |
|          2 | 2022-05-04 08:15:10 |
|          3 | 2022-05-04 08:15:10 |
|          1 | 2022-05-05 08:15:10 |
|          2 | 2022-05-05 08:15:10 |
|          3 | 2022-05-05 08:15:10 |
|          1 | 2022-05-06 08:15:10 |
|          2 | 2022-05-06 08:15:10 |
|          3 | 2022-05-06 08:15:10 |
+------------+---------------------+
15 rows in set (0.04 sec)

仅在 MySQL 中按 DATE 对 DATETIME 列进行分组

我们想查看周一到周五有多少学生上课。 我们将仅按 DATE 分组，如下所示。

示例代码：

SELECT COUNT(STUDENT_ID), DATE(ATTENDANCE)
FROM student_attendance
GROUP BY DATE(student_attendance.ATTENDANCE);

输出:

+-------------------+------------------+
| COUNT(STUDENT_ID) | DATE(ATTENDANCE) |
+-------------------+------------------+
|                 3 | 2022-05-02       |
|                 3 | 2022-05-03       |
|                 3 | 2022-05-04       |
|                 3 | 2022-05-05       |
|                 3 | 2022-05-06       |
+-------------------+------------------+
5 rows in set (0.00 sec)

或者，我们可以使用 ALIAS 使输出清晰。

示例代码：

SELECT COUNT(STUDENT_ID) AS NumbOfStudents, DATE(ATTENDANCE) AS DateOnly
FROM student_attendance
GROUP BY DATE(DateOnly);

输出:

+----------------+------------+
| NumbOfStudents | DateOnly   |
+----------------+------------+
|              3 | 2022-05-02 |
|              3 | 2022-05-03 |
|              3 | 2022-05-04 |
|              3 | 2022-05-05 |
|              3 | 2022-05-06 |
+----------------+------------+
5 rows in set (0.00 sec)

我们将 DATETIME 类型转换为 DATE 只是为了实现上述查询中的目标。 我们使用了不同的函数和子句，下面将对其进行简要说明。

MySQL COUNT() 函数

COUNT() 是一个聚合函数，我们用它来返回表达式的计数。 它让我们计算满足特定条件的所有表记录。

COUNT() 函数的返回类型是 BIGINT。 如果表中没有匹配的记录，则返回 0。

我们可以通过 3 种方式将 COUNT() 函数与 SELECT 语句一起使用。

1. COUNT(*) - 此表单生成的输出包含所有重复值、NULL 和 NOT NULL 值。
2. COUNT(表达式) - 此 COUNT() 函数返回的记录数不具有 NULL 值。
3. Count(distinct) - 它返回不具有 NULL 值的不同记录的数量作为表达式的结果。

MySQL DATE() 函数

DATE() 方法仅从 DATETIME 表达式中提取 DATE。 如果表达式不是有效的 DATETIME 或 DATE 值，则返回 NULL。

MySQL GROUP BY 子句

GROUP BY 子句可以将具有相同值的记录分组到汇总记录中。 例如，查找班级中的学生人数。

我们将此语句与聚合函数一起使用，以按单个或多个字段（列）对输出进行分组。