同表对比两个时间点open值SQL查询结果与预期不符问题

涉及的数据库结构如下：

原SQL存在的问题 #

• 语法逻辑错误：IN运算符的返回值为布尔类型（匹配成功返回1，失败返回0），你编写的WHERE条件实际是对两个布尔值的大小进行比较，完全没有实现两个指定时间点open值的对比逻辑。你原来的SQL返回3条数据，本质是满足1>0布尔比较条件的行有3条，和你想要的open值对比逻辑完全无关。
• 业务逻辑缺失：没有添加script_id的关联约束，两个子查询返回的是全表符合时间条件的open值集合，没有和外层查询的行做同一script_id的绑定，不符合「同一script_id下对比两个时间点open值」的需求。

正确的SQL写法 #

你可以通过自关联的方式实现需求，写法直观且执行效率更高：

SELECT t1.*
FROM data_five_minutes t1
INNER JOIN data_five_minutes t2 
  ON t1.script_id = t2.script_id 
  AND t2.date_time = '2019-01-02 13:15:00'
INNER JOIN data_five_minutes t3 
  ON t1.script_id = t3.script_id 
  AND t3.date_time = '2019-01-01 11:10:00'
WHERE t2.open > t3.open

如果你的数据库支持关联子查询，也可以用下面的写法：

SELECT *
FROM data_five_minutes t1
WHERE (
  SELECT open FROM data_five_minutes t2 
  WHERE t2.script_id = t1.script_id AND t2.date_time = '2019-01-02 13:15:00'
) > (
  SELECT open FROM data_five_minutes t3 
  WHERE t3.script_id = t1.script_id AND t3.date_time = '2019-01-01 11:10:00'
)

如果没有script_id满足两个时间点的open值大小条件，上述查询会返回0条结果，符合你的预期。

内容的提问来源于stack exchange，提问作者Kabomi