mysql - 按天汇总 MySQL 表条目

Question

我有一个包含 4 列的 MySQL 表: 完成的任务、持续时间、日期和教育部门。我的第一个目标是显示我在特定日期的条目按工作日的名称。我通过以下查询实现了这一点:

SELECT DAYNAME(date) AS DAY,
       completedtasks,
       duration,
       educationDepartment
FROM programm_completedtask;

现在我想按星期几总结我所拥有的信息。假设我有一个包含以下条目的表格:

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do      |        6 | A Department        |
| Tuesday   | Some other stuff to do|        8 | Another Department  |
| Wednesday | Some other stuff to do|        6 | Test Department     |
| Thursday  | Being lazy            |        8 | Another Test        |
| Friday    | A completed task      |        8 | Another Test        |
| Monday    | Some entry            |        2 | A Test              |
| Wednesday | Test entry            |        2 | Another Test        |
+-----------+-----------------------+----------+---------------------+

我尝试了几个查询，但没有给我想要的结果。当我使用 GROUP BY 时，它只给出了我在表中的条目，如下所示:

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do      |        6 | A Department        |
+-----------+-----------------------+----------+---------------------+

这个查询:

SELECT DAYNAME(date) AS Day,
       completedtasks,
       duration,
       educationDepartment
FROM programm_completedtask
WHERE DAYNAME(date)
LIKE "Monday";

给了我以下结果。

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do      |        6 | A Department        |
| Monday    | Some entry            |        2 | A Test              |
+-----------+-----------------------+----------+---------------------+

我的目标是获得这样的结果:

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do,     |       6,2| A Department, A Test|
|           | Some entry            |          |                     |
+-----------+-----------------------+----------+---------------------+

所以，我想总结一个特定工作日的条目，并且我希望它在一行中。我希望这在某种程度上是可能的。非常感谢您的帮助!

Answer 1

SELECT DAYNAME(date) as `Day`,
        GROUP_CONCAT(completedtasks ) as completedTasks,
        GROUP_CONCAT(duration ) as duration ,
        GROUP_CONCAT(educationDepartment ) as educationDepartment  ,
FROM tableName
GROUP BY DAYNAME(date)

无需指定SEPERATOR，默认为逗号