mysql - 查找 min(date) 和 min(date)+value sql 之间的记录

Question

我正在尝试在 2 个表之间选择一条记录

基于条件：

• 最高价

• min(date) 和 min(date)+20 年之间的日期范围

Select PRICE, NAME, ORDER_DATE 
 from customers a, orders b 
 where a.ORDER_ID = b.ID and 
b.PRICE =  (SELECT MAX(PRICE) FROM orders) and 
b.ORDER_DATE between '1900-01-01' and '2000-01-01'

我不确定设置日期范围的正确方法是什么

min(ORDER_DATE) 和 min(ORDER_DATE)+20 年

标签： mysql

Answer 1

对表格进行适当的连接，按价格降序排序并获得第一行：

SELECT b.PRICE, a.NAME, b.ORDER_DATE 
FROM customers a INNER JOIN orders b 
ON a.ORDER_ID = b.ID 
WHERE b.ORDER_DATE <= (SELECT MIN(ORDER_DATE) FROM orders) + INTERVAL 20 YEAR
ORDER BY b.PRICE DESC LIMIT 1

此查询不会返回平局。
如果你想要领带：

SELECT b.PRICE, a.NAME, b.ORDER_DATE 
FROM customers a INNER JOIN orders b 
ON a.ORDER_ID = b.ID 
WHERE b.ORDER_DATE <= (SELECT MIN(ORDER_DATE) FROM orders) + INTERVAL 20 YEAR
  AND b.PRICE = (SELECT MAX(PRICE) FROM orders WHERE ORDER_DATE <= (SELECT MIN(ORDER_DATE) FROM orders) + INTERVAL 20 YEAR)