如何修改SQL查询，仅当日差为0时添加小时差过滤条件？

原查询 #

select a.account_id AS ACCOUNT_ID, a.customer__id AS CUS_ID, b.first_Name AS NAME,
b.last_time - a.last_time AS LAST_TIME_DIFFERENCE
from tableA a, tableB b
where a.account_id = b.account_id AND a.customer__id = b.customer__id
AND EXTRACT(HOUR FROM (a.last_time - b.last_time)) > 6
ORDER BY b.first_name

需求 #

last_time是timestamp类型字段，要求仅当a.last_time与b.last_time的天数差为0时，才校验两者的小时差是否大于6；若天数差不为0，直接保留该条数据。

修改后的查询（两种可选写法） #

写法1：逻辑表达式组合 #

select a.account_id AS ACCOUNT_ID, a.customer__id AS CUS_ID, b.first_Name AS NAME,
b.last_time - a.last_time AS LAST_TIME_DIFFERENCE
from tableA a, tableB b
where a.account_id = b.account_id 
  AND a.customer__id = b.customer__id
  AND (
    -- 天数差不为0时，直接满足条件
    EXTRACT(DAY FROM (a.last_time - b.last_time)) != 0
    -- 天数差为0时，要求小时差大于6
    OR EXTRACT(HOUR FROM (a.last_time - b.last_time)) > 6
  )
ORDER BY b.first_name

写法2：CASE表达式判断 #

select a.account_id AS ACCOUNT_ID, a.customer__id AS CUS_ID, b.first_Name AS NAME,
b.last_time - a.last_time AS LAST_TIME_DIFFERENCE
from tableA a, tableB b
where a.account_id = b.account_id 
  AND a.customer__id = b.customer__id
  AND CASE 
        WHEN EXTRACT(DAY FROM (a.last_time - b.last_time)) = 0 
        THEN EXTRACT(HOUR FROM (a.last_time - b.last_time)) > 6
        ELSE TRUE
      END
ORDER BY b.first_name

说明 #

两种写法逻辑完全等价：

• 写法1用OR串联条件，逻辑直白，大部分数据库都能高效优化。
• 写法2用CASE做分支判断，可读性强，适合习惯用条件分支的场景。

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