最高/最低pl / sql

user4158998 发表于 Dev

用户名

我已制定了在给定日期的特定时间显示最受欢迎和最低人气商品的过程。该过程没有错误或异常，并且所有功能均正常运行。如您所见，为了显示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;
  /

乔恩·海勒

使用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')
);