sql - 获取最旧的 7 天记录

Question

我有查询获取最早 7 天的数据，但因“介于”而失败。我的查询:

SELECT b.sequence_number, b.message
FROM test.tab1 a, test.tab2 b
WHERE a.id = b.id 
AND a.delivery_date between (select min(delivery_date) from test.tab1) 
and (select min(delivery_date)-7 from test.tab1)

但是这个查询得不到结果。如何解决这个问题？

Answer 1

你需要固定顺序，先小后大

SELECT b.sequence_number, b.message
FROM test.tab1 a, test.tab2 b
WHERE a.id = b.id 
AND a.delivery_date between (select min(delivery_date)-7 from test.tab1)
and (select min(delivery_date) from test.tab1)