主页 / coding / 问题 / 79063200
Accepted
John Beasley
Asked: 2024-10-08 02:29:11 +0800 CST

排除一系列日期列中包含 3 个或更多日期的记录

  • 772

我正在使用使用 MySQL 版本 5.7 的 phpMyAdmin。

我有一个名为的数据库表condensed，其中包含数百万封具有多个“发送日期”和其他列的唯一电子邮件。有些电子邮件的发送日期为 NULL。

这里仅关注email和send_date列。

以下是该表的示例：

| email  | a_last_sent | b_last_sent | c_last_sent | d_last_sent | ..up to 14 dates
----------------------------------------------------------------------------------
| email1 | 2024-06-12  | 2024-05-25  |    NULL     | 2024-06-06  |
----------------------------------------------------------------------------------
| email2 | 2024-06-01  | 2024-06-16  |  2024-06-05 | 2024-06-19  |
----------------------------------------------------------------------------------
| email3 |   NULL      |   NULL      |  2024-05-12 | 2024-06-10  |
----------------------------------------------------------------------------------
| email4 |   NULL      | 2024-06-13  |    NULL     | 2024-05-11  |
----------------------------------------------------------------------------------
| email5 | 2024-06-09  | 2024-05-01  |    NULL     |    NULL     |
----------------------------------------------------------------------------------

我需要排除六月份内发送日期超过 3 个的所有内容。

因此，如果电子邮件的发送日期在 6 月份内的 3 列或以上，请不要返回这些记录。

使用我上面的例子，结果应该是这样的：

| email  | a_last_sent | b_last_sent | c_last_sent | d_last_sent | ..up to 14 dates
----------------------------------------------------------------------------------
| email1 | 2024-06-12  | 2024-05-25  |    NULL     | 2024-06-06  |
----------------------------------------------------------------------------------
| email3 |   NULL      |   NULL      |  2024-05-12 | 2024-06-10  |
----------------------------------------------------------------------------------
| email4 |   NULL      | 2024-06-13  |    NULL     | 2024-05-11  |
----------------------------------------------------------------------------------
| email5 | 2024-06-09  | 2024-05-01  |    NULL     |    NULL     |
----------------------------------------------------------------------------------

上面的结果被排除，email2因为它在六月份内有 4 个日期。

email1符合条件，因为它在 6 月份只有 2 个日期，在 5 月份只有 1 个日期。

email3、、email4和email5符合条件，因为它们在 6 月份只有 1 个日期，在 5 月份只有 1 个日期。

我可以编写以下查询：

SELECT 
  * FROM 
`condensed` 
WHERE 
(
  `a_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30'
  OR
  `b_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30'
  OR
  `c_last_sent` NOT BETWEEN '2024-06-01' AND '2024-06-30' 
  // remaining date columns     
)

上述查询排除了日期范围内的任何内容。

我只是不确定如何合并计数以排除指定日期范围内超过 3 个日期的任何内容（在本例中为 6 月）。

请帮忙，谢谢。

mysql

2 个回答

  1. Best Answer

    值包含 NULL - 因此我们无法使用大部分函数/运算符连接或求和表达式。

    我建议您使用 CONCAT_WS() 连接日期 - 此函数会跳过 NULL。然后计算相应的子字符串的数量。

    对于确定的月份，使用类似

    SELECT LENGTH(dateslist) - LENGTH(REPLACE(dateslist, '2024-06-', '2024-06')) AS amount, ...
FROM ( SELECT CONCAT_WS(',', a_last_sent, b_last_sent, ..., x_last_sent) AS dateslist, ...
       ...
       )
...

    对于自定义范围使用 UNION、嵌套 REPLACE（v.5.7 不支持正则表达式替换）或注册并根据用户定义的函数使用。

    当然您也可以使用多个COALESCE()。

    您还可以使用多个 UNION 来取消透视数据，然后按照常用方式进行计数。

    更新

    您介意举例说明 COALESCE 或 UNION 在这种情况下如何运作吗？– 约翰·比斯利

    使用 COALESCE。根据Roemer的回答编辑的代码

    ...
WHERE 
      (COALESCE(`a_last_sent`, '2000-01-01') BETWEEN '2024-06-01' AND '2024-06-30')
    + (COALESCE(`b_last_sent`, '2000-01-01') BETWEEN '2024-06-01' AND '2024-06-30')
    + (COALESCE(`c_last_sent`, '2000-01-01') BETWEEN '2024-06-01' AND '2024-06-30') 
...

    使用 UNION：

    ...
FROM ( SELECT email, a_last_sent FROM table WHERE a_last_sent IS NOT NULL
       UNION ALL
       SELECT email, b_last_sent FROM table WHERE b_last_sent IS NOT NULL
       UNION ALL
       SELECT email, c_last_sent FROM table WHERE c_last_sent IS NOT NULL
       UNION ALL
       ...
       ) AS unpivotted_data
...
    • 1

  2. 在 MySQL 中进行数学运算时，利用 TRUE 计为 1 的事实。

    SELECT * FROM `condensed` 
WHERE 
    (IFNULL(`a_last_sent`, 0) BETWEEN '2024-06-01' AND '2024-06-30')
    + (IFNULL(`b_last_sent`, 0) BETWEEN '2024-06-01' AND '2024-06-30')
    + (IFNULL(`c_last_sent`, 0) BETWEEN '2024-06-01' AND '2024-06-30') 
    // remaining date columns ... BTW bad table structure   
    <= 3;
    • -1

相关问题