时间戳

首页 > 解决方案 > MySQL 跨多列计数，带有子查询，带有一个结果查询

mysql - MySQL 跨多列计数，带有子查询，带有一个结果查询

问题描述

我有一个包含字段（task1、task2、..pts、user、date）的表（kt2020）。同一个任务可以出现在多个列中。要按任务计数，我有子查询：

SELECT
    task,
    count(*)
FROM     (SELECT task1 AS task FROM kt2020 UNION ALL
          SELECT task2 AS task FROM kt2020 UNION ALL
          SELECT task3 AS task FROM kt2020 UNION ALL
          SELECT task4 AS task FROM kt2020 UNION ALL
          SELECT task5 AS task FROM kt2020 UNION ALL
          SELECT task6 AS task FROM kt2020 UNION ALL
          SELECT task7 AS task FROM kt2020
) t

group by task

我现在想要的是创建一个外部查询，进一步按用户和日期过滤；像这样的smtg：

WHERE STR_TO_DATE(date, '%Y-%m-%d') >= CURDATE() - INTERVAL 7 DAY and user = 'username'

这会给我过去 7 天的任务结果，来自用户“用户名”。现在，这些外部字段对当前查询是隐藏的，我不知道如何在同一个查询中访问“日期”或“用户”。

我想我可以敲出一些又长又麻烦的东西，但我敢肯定有一种方法可以使用我不知道的常用功能？

标签： mysqlsubqueryaggregate-functions

解决方案

一种选择是返回子查询date，user然后使用WHERE子句进行过滤：

SELECT
    task,
    count(*)
FROM     (SELECT task1 AS task, date, user FROM kt2020 UNION ALL
          SELECT task2 AS task, date, user FROM kt2020 UNION ALL
          SELECT task3 AS task, date, user FROM kt2020 UNION ALL
          SELECT task4 AS task, date, user FROM kt2020 UNION ALL
          SELECT task5 AS task, date, user FROM kt2020 UNION ALL
          SELECT task6 AS task, date, user FROM kt2020 UNION ALL
          SELECT task7 AS task, date, user FROM kt2020
) t
WHERE STR_TO_DATE(date, '%Y-%m-%d') >= CURDATE() - INTERVAL 7 DAY and user = 'username'
group by task

推荐阅读