sql - 最高/最低 pl/sql

Question

我已经制定了一个程序来显示给定日期的特定时间的最高和最低流行项目。该过程没有错误或异常，并且一切正常。如您所见，为了显示 Items 的第一条记录，查询重复了两次，但唯一的区别在于顺序(ASC 和 DESC)!有什么办法可以减少代码量吗？如何在一个查询而不是两个查询中显示最高和最低的项目？我只想让代码整洁易读。任何帮助将非常感激。

CREATE OR REPLACE PROCEDURE highest_lowest(param IN DATE)
AS
  v_pno_low  NUMBER(5);
  v_pno_high NUMBER(5);
  BEGIN

    SELECT item_no INTO v_pno_low
    FROM (
      SELECT
        items.item_no,
        sum(items.quantity)                 AS total,
        to_char(prodcution_d, 'dd-mm-yyyy') AS pro_date
      FROM items
        JOIN parts ON parts.serial_no = items.serial_no
      GROUP BY item_no, to_char(prodcution_d, 'dd-mm-yyyy')
      ORDER BY sum(items.quantity) ASC)
    WHERE rownum = 1
          AND pro_date = to_char(param_date, 'mm-yyyy');

    dbms_output.put_line('LOWEST ITEM: ' || v_pno_low);

    SELECT item_no INTO v_pno_low
    FROM (
      SELECT
        items.item_no,
        sum(items.quantity)                 AS total,
        TO_CHAR(prodcution_d, 'dd-mm-yyyy') AS pro_date
      FROM items
        JOIN parts ON parts.serial_no = items.serial_no
      GROUP BY item_no, to_char(prodcution_d, 'dd-mm-yyyy')
      ORDER BY SUM(items.quantity) DESC)
    WHERE rownum = 1
          AND pro_date = to_char(param_date, 'mm-yyyy');

    dbms_output.put_line('HIGHEST POPULAR ITEM: ' || v_pno_high);

  END;
  /

Answer 1

使用 FIRST 和 LAST 函数在一个查询中同时获取最大值和最小值:

--Step 2: The SERIAL_NO for the smallest and largest quantity per day.
select
    min(serial_no) keep (dense_rank first order by quantity_sum) low_serial_no,
    min(serial_no) keep (dense_rank last  order by quantity_sum) high_serial_no
into v_pno_low, v_pno_high
from
(
    --Step 1: Sum of quantity, per day, for the specified month.
    select
        items.serial_no,
        trunc(production_date, 'day') the_date,
        sum(quantity) quantity_sum
    from items
    join parts
        on items.serial_no = parts.serial_no
    where trunc(production_date, 'month') = trunc(param_date, 'month')
    group by items.serial_no, trunc(production_date, 'day')
);

KEEP 语法起初有点困惑。外部查询获取 SERIAL_NO 的最小值，但仅限于第一个(最低)QUANTITY_SUM 和最后一个(最高)QUANTITY_SUM 的行。

查询还使用 TRUNC 而不是 TO_CHAR。通常最好将日期保留为日期并避免任何转换函数。