mysql按日期范围显示行和分组中的每种类型

shiro 发表于 Dev

四郎

我不确定该数据结构是否能够达到我想要的结果。http://sqlfiddle.com/#!9/84939

这是数据，请忽略工期列。

+----+---------------------+---------------------+---------------------+----------+--------+------+
| id | created_date        | start_date          | end_date            | duration | status | type |
+----+---------------------+---------------------+---------------------+----------+--------+------+
|  1 | 2016-04-05 15:23:29 | 2016-08-15 10:21:53 | 2016-08-19 00:00:00 |       30 |      1 |    2 |
|  2 | 2016-04-06 15:23:29 | 2016-08-15 10:21:53 | 2016-08-19 00:00:00 |       30 |      1 |    1 |
|  3 | 2016-04-06 15:23:29 | 2016-08-15 10:21:53 | 2016-08-19 00:00:00 |       30 |      1 |    3 |
|  4 | 2016-04-06 15:23:29 | 2016-08-17 10:21:53 | 2016-08-19 00:00:00 |       30 |      1 |    1 |
|  5 | 2016-04-06 15:23:29 | 2016-08-17 09:21:53 | 2016-08-19 00:00:00 |       30 |      1 |    1 |
|  6 | 2016-04-06 15:23:29 | 2016-08-01 09:21:53 | 2016-08-31 00:00:00 |       30 |      1 |    1 |
|  7 | 2016-04-06 15:23:29 | 2016-08-01 09:21:53 | 2016-08-31 00:00:00 |       30 |      0 |    1 |
|  8 | 2016-04-06 15:23:29 | 2016-08-15 09:21:53 | 2016-08-16 00:00:00 |       30 |      1 |    2 |
|  9 | 2016-04-06 15:23:29 | 2016-08-16 09:21:53 | 2016-08-17 00:00:00 |       30 |      1 |    3 |
| 10 | 2016-04-06 15:23:29 | 2016-08-19 09:21:53 | 2016-08-20 00:00:00 |       30 |      1 |    2 |
+----+---------------------+---------------------+---------------------+----------+--------+------+

我想过滤2016年8月15日至2016年8月19日之间的报告。对于2015-08-19甚至00:00:00，我不确定是否考虑计数。但以我为例。我只是数一数，因为它在范围内。这是我手动完成的摘要：

(type-2)15,16,17,18,19
(type-1)15,16,17,18,19
(type-3)15,16,17,18,19
(type-1)17,18,19
(type-1)17,18,19
(type-1)15,16,17,18,19
(type-1)15,16,17,18,19
(type-2)15,16
(type-3)16,17
(type-2)19,20

这是我想在sql返回数据中生成的结果。

+------------+--------+-----------+-----------+-----------+
| date       | ct_all | ct_type_1 | ct_type_2 | ct_type_3 |
+------------+--------+-----------+-----------+-----------+
| 2016-08-15 | 6      | 3         | 2         | 1         |
| 2016-08-16 | 7      | 3         | 2         | 2         |
| 2016-08-17 | 8      | 5         | 1         | 2         |
| 2016-08-18 | 7      | 5         | 1         | 1         |
| 2016-08-19 | 8      | 5         | 2         | 1         |
+------------+--------+-----------+-----------+-----------+

ct_all =全部计数

ct_type_1 =类型1的总数

只要类型属于start_date和end_date，它就会计数。通常，我们完成的搜索日期基于一种列类型，例如created_date。我可以在> =和<=之间使用查找范围。但是这个有开始日期和结束日期。不确定是否可以实现。

琼斯

您在这里发生了三件事。

天的枚举。
一个DATETIME范围的过滤器。
所谓的数据透视表，即按类型将行透视成列。

一次服用这些药物很有帮助。

首先，我想您有5天要过滤，[2016年8月15日-2016年8月19日]。您想列出所有这些天。这个小查询可以做到这一点。（http://sqlfiddle.com/#!9/84939/21/0）

    SELECT CONVERT('2016-08-15' + INTERVAL seq DAY, DATETIME) AS CURDATE
        FROM (SELECT 0 AS SEQ UNION ALL SELECT 1 UNION ALL SELECT 2
                              UNION ALL SELECT 3 UNION ALL SELECT 4
             ) seq_0_to_4

（注意：MySQL的MariaDB分支具有内置的序列表seq_0_to_4，因此您不必做所有这些UNION ALL事情。）

其次，您想要获取type每天发生的值的列表。您可以使用LEFT JOIN来做到这一点，例如（http://sqlfiddle.com/#!9/84939/26/0）。

 SELECT seq.curdate, record.type
   FROM (

          SELECT CONVERT('2016-08-15' + INTERVAL seq DAY, DATETIME) AS CURDATE
            FROM (SELECT 0 AS SEQ UNION ALL SELECT 1 UNION ALL SELECT 2
                                  UNION ALL SELECT 3 UNION ALL SELECT 4
                 ) seq_0_to_4
       ) seq
  LEFT JOIN record ON seq.curdate >= DATE(record.start_date)
                  AND seq.curdate <= DATE(record.end_date)

这为您提供curdate和的列表type。

该ON联接的条件选择record在每个日期或之前开始并在每个日期的任何时间结束的行。

最后，您需要执行一项pivot操作以汇总类型值的计数。看起来像这样。（http://sqlfiddle.com/#!9/84939/28/0）

 SELECT curdate, 
        COUNT(type) ct_all,
        SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) ct_1,
        SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) ct_2,
        SUM(CASE WHEN type = 3 THEN 1 ELSE 0 END) ct_3
   FROM (the above query) d
  GROUP BY curdate
  ORDER BY curdate

在这种情况下，结构化查询语言的结构化部分是必需的。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。