使用多个类似的内部查询优化选择查询

Question

我一直在一个大的表上写一个查询。我根本不喜欢数据库。查询看起来很大，我觉得可以缩短，但似乎找不到方法。我尝试使用 WITH 子句，这将使查询更简单。但是，我不能使用它。

以下是查询：

SELECT id, 
name, 
Count(name), 
SUM(event.time_spent_millis), 
flag, 
template 
FROM   event 
WHERE  event.host = 'mantis' 
AND event .` input ` NOT LIKE '%random%' 
AND id IN (SELECT event1.id 
FROM   event AS event1 
WHERE  ( event1.host = 'mantis' 
AND timestamp BETWEEN 1559337058633 AND 1559683282607 
AND template IN ( 'wrap' ) 
AND event1.flag = '22' 
AND event1.name = 'jack' )) 
OR id IN (SELECT a.id 
FROM   event AS a 
WHERE  a .` input ` LIKE '%random%' 
AND a.name = 'jack' 
AND a.host = 'mantis' 
AND template IN ( 'wrap' ) 
AND timestamp BETWEEN 1559337058633 AND 1559683282607 
AND a.flag IN ( '0', '1' ) 
AND a.id NOT IN (SELECT b.id 
FROM   event AS b 
WHERE  b.flag = '22' 
AND b.host = 'mantis' 
AND b.timestamp BETWEEN 
1559337058633 AND 
1559683282607 
AND b.name = 'jack')) 
GROUP  BY id, 
name 

示例数据：

id  name    time    flag    template
aaa123  jack    6561    22  wrap
aaa123  matt    18  NULL    NULL
aaa123  matt1   1126    0   NULL
baa123  jack    6561    22  wrap
baa123  matt    18  NULL    NULL
baa123  matt1   1126    0   NULL

总结一下我想要的结果是：

对于给定的输入，我想要与标志相关的所有名称以及与该名称关联的名称。

在给定的数据中，输出将标识标志22，杰克有2个计数，其他有4个计数，时间是所有(包括杰克和其他)的总和。

查询没有给出所需的输出，因为它变得越来越复杂。

Answer 1

我认为，以下代码是使用子查询的SQL的优化版本

SELECT id, 
name, 
Count(name), 
SUM(event.time_spent_millis), 
flag, 
template 
FROM   event e
WHERE  e.host = 'mantis' 
e.timestamp BETWEEN 1559337058633 AND 1559683282607 
AND e.template = 'wrap' 
AND e.flag = '22' 
AND e.name = 'jack'
AND (
(e .` input ` NOT LIKE '%random%' AND e.flag = '22')
OR
(e .` input ` LIKE '%random%' AND e.flag IN ( '0', '1' ))
)
GROUP  BY e.id, 
e.name