如何使用PL/SQL子查询查询所有产品对应的最高无折扣单价

测试环境准备 #

可执行以下脚本构造测试表和测试数据，若本地库不存在Orders、Products父表，可注释掉建表语句末尾的两个外键约束，不影响本次功能测试：

CREATE TABLE OrderDetails 
(OrderID  NUMBER NOT NULL, 
  ProductID  NUMBER NOT NULL, 
  UnitPrice  NUMBER NOT NULL, 
  Quantity  NUMBER NOT NULL, 
  Discount  NUMBER NOT NULL, 
  CONSTRAINT PK_Order_Details 
  PRIMARY KEY (OrderID, ProductID), 
  CONSTRAINT CK_Discount   CHECK ((Discount >= 0 and Discount <= 1)), 
  CONSTRAINT CK_Quantity   CHECK ((Quantity > 0)), 
  CONSTRAINT CK_UnitPrice   CHECK ((UnitPrice >= 0))
  -- 无父表可注释以下两行外键约束
  -- ,CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY (OrderID) REFERENCES Orders(OrderID), 
  -- CONSTRAINT FK_OrderDetails_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10248, 11, 14.0000, 12, 0);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10248, 42, 9.8000, 10, 0);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10248, 72, 34.8000, 5, 0);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10249, 14, 18.6000, 9, 0);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10249, 51, 42.4000, 40, 0);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10250, 41, 7.7000, 10, 0);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10250, 51, 42.4000, 35, 0.15);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10250, 65, 16.8000, 15, 0.15);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10251, 22, 16.8000, 6, 0.05);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10251, 57, 15.6000, 15, 0.05);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10251, 65, 16.8000, 20, 0);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10252, 20, 64.8000, 40, 0.05);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10252, 33, 2.0000, 25, 0.05);
INSERT INTO OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10252, 60, 27.2000, 40, 0);

实现代码 #

写法1：嵌套子查询实现（性能更优） #

先通过子查询筛选所有无折扣的订单明细，再按产品ID分组取最大单价：

SELECT 
    ProductID,
    MAX(UnitPrice) AS 最高无折扣单价
FROM (
    SELECT ProductID, UnitPrice
    FROM OrderDetails
    WHERE Discount = 0
) t
GROUP BY ProductID
ORDER BY ProductID;

写法2：关联子查询实现 #

通过关联外层查询的产品ID，逐行计算对应产品的最高无折扣单价：

SELECT DISTINCT
    o1.ProductID,
    (
        SELECT MAX(o2.UnitPrice)
        FROM OrderDetails o2
        WHERE o2.ProductID = o1.ProductID AND o2.Discount = 0
    ) AS 最高无折扣单价
FROM OrderDetails o1
ORDER BY o1.ProductID;

结果验证 #

基于提供的测试数据，执行以上语句会返回所有产品的最高无折扣单价，例如：

• 产品51的无折扣最高单价为42.4
• 产品65的无折扣最高单价为16.8
• 只有折扣记录、无对应无折扣记录的产品（如22、33等）最高无折扣单价返回空值

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