我在这个sql编码中是新手，我正在尝试学习新东西。因此，我创建了一个带有一些属性的交货表，例如物料代码，交货日期，交货数量。所以 如何从同一张表中获得第一个交货日期（最小日期）和交货数量以及最新交货日期（最大日期）及其数量？
我尝试使用union子句获取数量的最小值（日期）和最大值（日期）。但是输出结果却不像我想象的那样
这是我尝试过的
...
statetimeConfig.labels = data.map(item => moment(item.state_time).format('HH:mm'))
...
我希望输出看起来像
您可以使用analytical functions如下：
SQL> CREATE TABLE DELIVERY (
2 ITEM_CODE NUMBER,3 DATE_ DATE,4 QUANTITY NUMBER
5 );
Table created.
SQL>
SQL> INSERT INTO DELIVERY VALUES (
2 110192,3 DATE '2019-05-05',4 30
5 );
1 row created.
SQL>
SQL> INSERT INTO DELIVERY VALUES (
2 110192,3 DATE '2019-01-01',4 10
5 );
1 row created.
SQL>
SQL> INSERT INTO DELIVERY VALUES (
2 110192,3 DATE '2019-10-10',4 25
5 );
1 row created.
SQL>
SQL> INSERT INTO DELIVERY VALUES (
2 110192,3 DATE '2019-08-08',4 19
5 );
1 row created.
SQL>
SQL> SELECT DISTINCT
2 ITEM_CODE,3 MIN(DATE_) OVER(
4 PARTITION BY ITEM_CODE
5 ) AS FIRST_DELIVERY_DATE,6 MAX(QUANTITY) KEEP(DENSE_RANK FIRST ORDER BY DATE_) OVER(
7 PARTITION BY ITEM_CODE
8 ) AS FIRST_QUANTITY,9 MAX(DATE_) OVER(
10 PARTITION BY ITEM_CODE
11 ) AS LATEST_DELIVERY_DATE,12 MAX(QUANTITY) KEEP(DENSE_RANK FIRST ORDER BY DATE_ DESC NULLS LAST) OVER(
13 PARTITION BY ITEM_CODE
14 ) AS LATEST_QUANTITY
15 FROM
16 DELIVERY;
ITEM_CODE FIRST_DEL FIRST_QUANTITY LATEST_DE LATEST_QUANTITY
---------- --------- -------------- --------- ---------------
110192 01-JAN-19 10 10-OCT-19 25
SQL>
干杯！
,解决此问题的一种方法是制作一个商品的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
要为一个特定的item_code选择结果，请在派生表中添加一个WHERE子句。这样会将该表的结果限制为该item_code的行，从而使后续JOIN的效率更高。例如：
SELECT m.item_code,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