mysql - 如何从同一张表中获取最小(日期)、最大(日期)及其数量？

Question

我是这个 sql 编码的新手，我正在尝试学习新的东西。因此，我创建了一个交货表，其中包含一些属性，如商品代码、交货日期、交货数量。所以如何从同一张表中获取第一个交货日期(最小日期)和交货数量以及最晚交货日期(最大日期)及其交货数量？

我尝试使用 union 子句来获取最小(日期)和最大(日期)及其数量。但是输出并没有像我想象的那样显示

这是我尝试过的

SELECT item_code , MAX(date) AS "Latest_delivery_date", quantity
FROM delivery
WHERE item_code='110192'
GROUP BY item_code
union all
SELECT item_code , MIN(date) AS "First_delivery_date", quantity
FROM delivery 
WHERE item_code='110192'
GROUP BY item_code;

我希望输出看起来像

Answer 1

解决此问题的一种方法是为一个项目创建 MIN 和 MAX 交货日期的派生表，然后 JOIN 返回到表中查找这些日期的数量。例如:

SELECT m.item_code, 
       m.min_date AS "First_delivery_date",  d1.quantity AS "First_quantity",
       m.max_date AS "Latest_delivery_date", d2.quantity AS "Latest_quantity"
FROM (SELECT item_code, MAX(date) AS max_date, MIN(date) AS min_date
      FROM delivery
      GROUP BY item_code) m
JOIN delivery d1 ON d1.item_code = m.item_code AND d1.date = m.min_date
JOIN delivery d2 ON d2.item_code = m.item_code AND d2.date = m.max_date

Demo on dbfiddle

要为一个特定的 item_code 选择结果，请将 WHERE 子句添加到派生表。这会将该表的结果限制为仅包含该 item_code 的行，从而使后续的 JOIN 更加高效。例如:

SELECT m.item_code, 
       m.min_date AS "First_delivery_date",  d1.quantity AS "First_quantity",
       m.max_date AS "Latest_delivery_date", d2.quantity AS "Latest_quantity"
FROM (SELECT item_code, MAX(date) AS max_date, MIN(date) AS min_date
      FROM delivery
      WHERE item_code = '010997'
      GROUP BY item_code) m
JOIN delivery d1 ON d1.item_code = m.item_code AND d1.date = m.min_date
JOIN delivery d2 ON d2.item_code = m.item_code AND d2.date = m.max_date