mysql - 通过比较时间来获取和分组数据

Question

问题:

我有 4 个表 A、B、C 和 D。

每个表都有相同的字段:id、日期、价格。

我使用内部联接来显示 x 日期每个产品 A、B、C 和 D 的价格:

  SELECT
  products.A.date as date,
  products.A.price as A_Price,
  products.B.price as B_Price,
  products.C.price as C_Price,
  products.D.price as D_Price

  FROM products.A

  INNER JOIN products.B ON products.B.date = products.A.date
  INNER JOIN products.C ON products.C.date = products.A.date
  INNER JOIN products.D ON products.D.date = products.A.date;

但是，我还想显示 x 日期 1 小时后每种产品的价格。

例如:

Answer 1

SELECT A1.date as ActualDate, A1.price as A_price, A2.price as A_afterOnehr,
B1.price as B_price, B2.price as B_afterOnehr
FROM productA A1 
INNER JOIN productA A2 ON date_add(A1.date, INTERVAL 1 hour) = A2.date
INNER JOIN productB B1 
ON A1.date=B1.date INNER JOIN productB B2
ON  date_add(B1.date, INTERVAL 1 hour) = B2.date   

这是表 A，B 也作为示例...

这是输出