时间戳

首页 > 解决方案 > 通过在 SQL 中的日期范围内无法正常工作进行分组

mysql - 通过在 SQL 中的日期范围内无法正常工作进行分组

问题描述

我有一张这样的桌子：

created_date  | id | status | completed_date
2019-03-20    | 1  | Open   | 
2019-03-20    | 2  | Open   | 
2019-03-19    | 3  | Comp   | 2019-03-21
2019-03-21    | 4  | Comp   | 2019-03-22
2019-03-22    | 5  | Comp   | 2019-03-22
2019-03-18    | 6  | Open   |

我想查找之前创建的所有状态为OR的IDs 的计数，它们是之前创建的，甚至具有状态但它们是在 之后完成的。'2019-03-21''Open''2019-03-21''Comp''2019-03-21'

以下是我的查询：

SELECT 
    CAST(CREATED AS DATE),
    COUNT(DISTINCT id)
FROM testtable
WHERE
    (CAST(CREATED AS DATE) <= '2019-03-21' AND status = 'Open')
    OR (
        CAST(CREATED AS DATE) <= '2019-03-21' AND status='Comp' 
        AND CAST(COMPLETED AS DATE) > '2019-03-21' 
    )

它给出了正确的结果。即，21 日，有 4 个 ID 被打开。但现在我想要过去 4 天的这些信息。我该如何修改这个查询来做到这一点？

输出应该是：

created_date | count(ID)
2019-03-21   | 4
2019-03-20   | 4
2019-03-19   | 2
2019-03-18   | 1

请帮忙！！

标签： mysqlsqlgroup-by

解决方案

这是一个适用于您的示例数据的解决方案。它使用可以在 column 中找到的不同值生成日期列表created_date，然后LEFT JOIN将其与 table 一起使用。这些JOIN条件带有您描述的逻辑。在我看来，您似乎不需要检查status记录，因为显然，任何具有非NULL completed_date状态的记录Comp。

SELECT 
    dt.created_date,
    COUNT(t.id)
FROM 
    (SELECT DISTINCT created_date FROM mytable) dt
    LEFT JOIN mytable t
        ON t.created_date <= dt.created_date 
        AND (t.completed_date IS NULL OR t.completed_date > dt.created_date)
GROUP BY dt.created_date

DB Fiddle上的这个演示与您的示例数据返回：

| created_date | COUNT(t.id) |
| ------------ | ----------- |
| 2019-03-18   | 1           |
| 2019-03-19   | 2           |
| 2019-03-20   | 4           |
| 2019-03-21   | 4           |
| 2019-03-22   | 3           |

推荐阅读