如何修改Oracle查询，将未采购商品以0采购量置末行

现有数据库环境中，商品ID为105、106的两个商品无采购记录。需要修改最后一条采购量统计查询，将这两个商品也纳入结果，显示其采购量为0，且在按采购量降序排序时处于结果最后两行。尝试使用LEFT JOIN未成功，寻求解决方案。

现有数据库环境代码 #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'John', 'Doe' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Presto 6-quart Pressure Cooker', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Cuisinart 8-quart Pressure Cooker', 111.99 FROM DUAL UNION ALL
SELECT 102, 'Farberware 3-quart Pressure Cooker', 49.99 FROM DUAL UNION ALL
SELECT 103, 'Farberware 6-quart Pressure Cooker', 89.29 FROM DUAL UNION ALL
SELECT 104, 'Farberware 8-quart Pressure Cooker', 105.99 FROM DUAL UNION ALL 
SELECT 105, 'Breville Fast Slow Pro Pressure Cooker 3 quart', 39.95 FROM DUAL UNION ALL 
SELECT 106, 'Breville Fast Slow Pro Pressure Cooker 6 quart', 59.95 FROM DUAL;


create table purchases(
  ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  customer_id   number, 
  PRODUCT_ID NUMBER, 
  QUANTITY NUMBER, 
  purchase_date timestamp
);

insert  into purchases (customer_id, product_id, quantity, purchase_date) 
select 1, 99+LEVEL, 
2, TIMESTAMP '2024-04-03 05:18:03' + numtodsinterval ( (LEVEL -1) * 1, 'day' ) + numtodsinterval ( LEVEL * 37, 'minute' ) +  numtodsinterval ( LEVEL * 3, 'second' ) FROM    dual CONNECT BY  LEVEL <= 5;


-- 查询未被采购的商品
select * from items i
where not exists
(select 1 from purchases p where p.product_id = i.PRODUCT_ID);

-- 原采购量统计查询（未包含未采购商品）
with prep (product_id, tot_quantity) as 
(
   select 
      product_id, 
      sum(quantity)
    from   purchases
    group  by product_id
  )
SELECT  
     p.product_id, 
     I.product_name,
  tot_quantity,
     sum(tot_quantity * i.PRICE) "TOTAL_AMT"
 FROM prep p
JOIN items i ON i.product_id = p.product_id  
GROUP BY  p.product_id, i.product_name, tot_quantity 
ORDER BY tot_quantity desc, product_id;

解决方案及修改后的查询 #

原查询未成功的原因是从采购统计结果（prep表）出发关联商品表，只会返回有采购记录的商品。要包含所有商品，需要从items表出发，LEFT JOIN采购统计结果，同时处理空值并调整排序逻辑：

with prep (product_id, tot_quantity) as 
(
   select 
      product_id, 
      sum(quantity)
    from   purchases
    group  by product_id
  )
SELECT  
     i.product_id, 
     i.product_name,
     NVL(p.tot_quantity, 0) as tot_quantity,
     NVL(p.tot_quantity, 0) * i.PRICE as TOTAL_AMT
 FROM items i
LEFT JOIN prep p ON p.product_id = i.product_id  
ORDER BY 
    -- 让未采购商品（tot_quantity为null）排在最后
    CASE WHEN p.tot_quantity IS NULL THEN 1 ELSE 0 END,
    tot_quantity desc,
    product_id;

关键改动说明 #

1. 关联方向调整：从items表LEFT JOINprep表，确保所有商品都被包含，无采购记录的商品会返回prep表字段为null
2. 空值处理：用NVL(p.tot_quantity, 0)将null的采购量转为0，同时计算总金额时也用该表达式避免null结果
3. 排序逻辑优化：新增CASE WHEN p.tot_quantity IS NULL THEN 1 ELSE 0 END作为第一排序条件，让未采购商品（原tot_quantity为null）排在所有有采购记录的商品之后，再按采购量降序、商品ID排序

内容的提问来源于stack exchange，提问作者Pugzly