选择在特定日期之后没有活动的客户

Question

我有两个表涉及这种情况：

tools_events

COLUMN_NAME	DATA_TYPE
event_id	int
event_type	varchar
event_value	varchar
event_client	int
event_timestamp	datetime

tools_clients

COLUMN_NAME	DATA_TYPE
client_id	int
client_name	varchar
client_developer	int

这是使用 MySQL 5.6 。

事件表显然存储一天中发生的事件，而客户端包含客户端列表。

我正在寻找缺乏的特定事件。对于此特定事件，event_type 将为 taskUpdated，event_code 将为需要批准 (dev)。

目前，我有一个声明放在一起，将拉出所有在指定日期之后已获得记录事件的客户端，该声明似乎运行良好，尽管速度非常慢：

SELECT 
    t1.event_id, t1.event_user, t1.event_client, t1.event_timestamp
FROM 
    tools_events AS t1 
WHERE 
    t1.event_id = (SELECT t2.event_id 
                   FROM tools_events AS t2 
                   WHERE t2.event_type = 'taskUpdated' 
                     AND t2.event_value = 'needs approval (dev)' 
                     AND t2.event_client = t1.event_client 
                     AND t2.event_timestamp >= '2022-04-04 00:00:00' 
                   ORDER BY t2.event_timestamp DESC LIMIT 1)

我正在寻找的是一种方法来自 tools_clients 的 client_id、client_name 和 client_developer（适用于在指定日期之后尚未记录此事件的客户端） 。由于无法将此列表拉入 PHP 并交叉引用完整的客户端列表来查看哪些客户端不存在，所以我不确定如何在 SQL 查询中解决此问题。

另一方面，似乎只要获取与初始条件匹配的事件列表就可以立即运行，因此也许直接提取这些事件并在 PHP 中执行所有逻辑会更有效？随着我对 SQL 的学习越来越深入，我的理解是，如果你能够正确地优化查询，那么 SQL 通常会更高效，但我认为我做不到。

以下是 tools_clients 表的一些示例数据：

client_id	client_name	client_developer
1	foo	12
2	fooBar	15
3	Bar	21
4	raBoof	37

以下是 tools_events 表的一些示例数据：

event_id	event_type	event_value	event_client	event_timestamp
23	taskUpdated	需要批准（开发）	1	2022-04-02 13:08:22
25	taskUpdated	需要批准 (dev)	2	2022-04-02 14:22:07
28	taskUpdated	需要批准 (dev)	3	2022-04-02 15:09:13
29	taskUpdated	需要批准（开发）	4	2022-04-02 15:36:17
32	taskUpdated	需要批准（开发）	3	2022-04-05 16:42:35
38	taskUpdated	需要批准（开发）	4	2022-04-05 19:01:25

鉴于限制：

WHERE event_type = 'taskUpdated' 
  AND event_value = 'needs approval (dev)' 
  AND event_timestamp >= '2022-04-04 00:00:00' 

我会寻找结果如下，因为这是唯一两个在指定日期之后没有发生匹配事件的结果：

client_id	client_name	client_developer
1	foo	12
2	fooBar	15

对此的任何帮助，即使它是正确方向的一点，我们也非常感激。

---

编辑：

使用 Paul Maxwell 在他的答案中提供的原始查询的简化版本，作为一个子查询和更多的谷歌搜索，我想我能够得到一些东西。我的理解是子查询比 JOIN 效率低，所以我不确定这是否是一个可靠的解决方案，但它似乎运行良好：

SELECT client_id, client_name, client_developer
FROM `tools_clients` AS clients
WHERE client_status > 0
  AND NOT EXISTS (SELECT event_client
                  FROM tools_events AS events
                  WHERE event_type = 'taskUpdated'
                    AND event_value = 'needs approval (dev)'
                    AND event_timestamp >= '2022-04-04 00:00:00'
                    AND clients.client_id = events.event_client
                  GROUP BY event_client)

旁注：我知道 client_status 中没有提到原来的问题。目前我并不担心这部分，因为我知道一旦我得到了一些可以正常工作的东西，我就可以轻松地添加这种情况。

Answer 1

现在提供的示例数据确实有帮助（很多），但是它不包含引用的某些列，也没有包含将为您寻求查询的结果提供结果的数据。因此，我添加了一些列和数据，并修改了一些数据以适合。

INSERT INTO tools_clients(client_id,client_name,client_developer)
VALUES (1,'foo',12),
       (2,'fooBar',15),
       (3,'Bar',21),
       (4,'raBoof',37),
       (5,'2answer-or-not',67); --<< added

INSERT INTO tools_events(event_id,event_type,event_value,event_client,event_timestamp)
VALUES
 (23,'taskUpdated','needs approval (dev)',1,'2022-04-04 13:08:22'), -- date change
 (25,'taskUpdated','needs approval (dev)',2,'2022-04-04 14:22:07'), -- date change
 (28,'taskUpdated','needs approval (dev)',3,'2022-04-04 15:09:13'), -- date change
 (29,'taskUpdated','needs approval (dev)',4,'2022-04-04 15:36:17'), -- date change
 (32,'taskUpdated','needs approval (dev)',3,'2022-04-05 16:42:35'),
 (38,'taskUpdated','needs approval (dev)',4,'2022-04-05 19:01:25');

INSERT INTO tools_events(event_id,event_type,event_value,event_client,event_timestamp) 
VALUES (138,'not-taskUpdated','not-needs approval (dev)',4,'2022-04-05 19:01:25'); -- added

因此，我们可以运行一个查询（基于所讨论的原始子查询）：

SELECT
      t1.event_id
    , t1.event_user
    , t1.event_client
    , t1.event_timestamp
FROM tools_events AS t1
WHERE  (
        t1.event_type = 'taskUpdated'
        AND t1.event_value = 'needs approval (dev)'
        )
    AND t1.event_timestamp >= '2022-04-04 00:00:00'

+----------+------------+--------------+-----------------+
| event_id | event_user | event_client | event_timestamp |
+----------+------------+--------------+-----------------+
|       23 |            |            1 | 2022-04-04      |
|       25 |            |            2 | 2022-04-04      |
|       28 |            |            3 | 2022-04-04      |
|       29 |            |            4 | 2022-04-04      |
|       32 |            |            3 | 2022-04-05      |
|       38 |            |            4 | 2022-04-05      |
+----------+------------+--------------+-----------------+

该查询的否定

SELECT
      t1.event_id
    , t1.event_user
    , t1.event_client
    , t1.event_timestamp
FROM tools_events AS t1
WHERE  NOT (
        t1.event_type = 'taskUpdated'
        AND t1.event_value = 'needs approval (dev)'
        )
    AND t1.event_timestamp >= '2022-04-04 00:00:00'

+----------+------------+--------------+-----------------+
| event_id | event_user | event_client | event_timestamp |
+----------+------------+--------------+-----------------+
|      138 |            |            4 | 2022-04-05      |
+----------+------------+--------------+-----------------+

并通过使用not（...）：注意event_client 4存在 。在这两个结果中，但没有提及event_client 5，

如果您正在寻找具有t1.event_type ='taskupdated'和t1的事件的客户端， 则稍后很重要.event_value ='需要批准（dev）' plus not（t1.event_type ='taskupedupded'和t1.event_value ='需求批准（dev）'然后这将产生结果：

SELECT client_id, client_name, client_developer
FROM `tools_clients` AS clients
WHERE client_id IN (
    SELECT
          t1.event_client
    FROM tools_events AS t1
    WHERE  NOT (
            t1.event_type = 'taskUpdated'
            AND t1.event_value = 'needs approval (dev)'
            )
    AND t1.event_timestamp >= '2022-04-04 00:00:00')


+-----------+-------------+------------------+
| client_id | client_name | client_developer |
+-----------+-------------+------------------+
|         4 | raBoof      |               37 |
+-----------+-------------+------------------+

但是，如果您只希望没有符合这些条件的事件的客户代码>然后有2个选项

SELECT client_id, client_name, client_developer
FROM `tools_clients` AS clients
left join tools_events AS events ON clients.client_id = events.event_client
        AND events.event_type = 'taskUpdated'
        AND events.event_value = 'needs approval (dev)'
        AND events.event_timestamp >= '2022-04-04 00:00:00'
WHERE client_status > 0
  AND events.event_client IS NULL

。一个不存在（也称为左半结合）;

SELECT client_id, client_name, client_developer
FROM `tools_clients` AS clients
WHERE client_status > 0
  AND NOT EXISTS
    (SELECT NULL
      FROM tools_events AS events
      WHERE event_type = 'taskUpdated'
        AND event_value = 'needs approval (dev)'
        AND event_timestamp >= '2022-04-04 00:00:00'
        AND clients.client_id = events.event_client
      )

这两个查询结果均为：

+-----------+----------------+------------------+
| client_id |  client_name   | client_developer |
+-----------+----------------+------------------+
|         5 | 2answer-or-not |               67 |
+-----------+----------------+------------------+

注意使用时，>或不存在子查询的选择子句不必“返回”任何内容，因此您可以使用select null或选择1或选择 *。这是因为存在只是测试是否存在/where子句的标准。另外，由于Select子句并没有真正“返回”任何内容，因此不需要在不存在子查询中使用（或选择不同的）组。以这种形式的子查询浪费了努力，以这种形式的子查询将该组纳入该组也不太效率。

NB：这与在Select子句“返回”数据“返回”数据的情况下使用非常不同。

因此，希望您能看到有多种否定条件的方法 - 但是如何否定它们可以产生不同的总体结果。要查看所有运行使用的查询： db＆lt; /a>

Answer 2

A simple way to negate a set of where clause predicates is to use NOT(...)

SELECT
      t1.event_id
    , t1.event_user
    , t1.event_client
    , t1.event_timestamp
FROM tools_events AS t1
WHERE NOT (
        t1.event_type = 'taskUpdated'
        AND t1.event_value = 'needs approval (dev)'
        )
    AND t1.event_timestamp >= '2022-04-04 00:00:00'
ORDER BY t1.event_timestamp DESC LIMIT 1

and your existing query could be simplified too:

SELECT
      t1.event_id
    , t1.event_user
    , t1.event_client
    , t1.event_timestamp
FROM tools_events AS t1
WHERE t1.event_type = 'taskUpdated'
    AND t1.event_value = 'needs approval (dev)'
    AND t1.event_timestamp >= '2022-04-04 00:00:00'
ORDER BY t1.event_timestamp DESC LIMIT 1

You do not specify which database or version, but if your db/ version support window functions such as row_number() then you might be able to get both records in one query like this:

SELECT
      event_id
    , event_user
    , event_client
    , event_timestamp
FROM (
    SELECT 
          event_id
        , event_user
        , event_client
        , event_timestamp
        , row_number() OVER (
            PARTITION BY CASE WHEN event_type = 'taskUpdated'
                    AND event_value = 'needs approval (dev)' THEN 1 ELSE 0 END
            ORDER BY event_timestamp DESC
            ) AS rn
    FROM tools_events
    WHERE event_timestamp >= '2022-04-04 00:00:00'
    ) AS d
WHERE rn = 1

this divdes the data into 2 "partitions" and for each of those the row with the latest event timestamp will get a row number 1。因此，两个想要的行都在一个查询中返回。