SQL查询：选择子选择的最大值

Johnny90 发表于 Dev

约翰尼90

我有一个已经可以运行的sql语句，但是我认为必须有比我更好的解决方案。

我试图获得该物品与最高价格已经从未售出。

通过此选择，我可以获得尚未售出的所有商品（数量+价格）：

select anr, price
from article a 
where not exists(
    select 1 from OrderItems o
    where o.artnr = a.anr 
  )

商品编号+价格结果看起来像

| Anr | Price |
| 1   | 300.0 |
| 4   | 340.0 |
| 5   | 340.0 |
| 3   | 200.0 |

我以最高价格获得商品的临时解决方案是：

select anr, price 
from article 
where anr in(
  select anr
  from article a 
  where not exists(
    select 1 from OrderItems o
    where o.artnr = a.anr 
  )
)
and price = (
  select max(price)
  from article a 
  where not exists(
    select 1 from OrderItems o
    where o.artnr = a.anr 
  )
)

正确的解决方案是：

| Anr | Price |
| 4   | 340.0 |
| 5   | 340.0 |

有没有办法避免两次相同的子选择？

为了进行测试，这里是带有我的插入值的缩短的“创建表”脚本：

CREATE TABLE Article
(
  Anr Int Primary Key,
  Price Numeric(9,2) Not Null
);

CREATE TABLE Orders
(
  OrderNr Int Primary Key
)

CREATE TABLE OrderItems
(
  OrderNr Int References Orders On Delete Cascade, 
  ItemNr  Int,
  Artnr   Int References Article Not Null,
  Amount  Int Not Null Check(Amount >= 0),
  Primary Key(OrderNr, ItemNr)
)

-- articles without an order
Insert into Article (Anr, Price) values(1,300.0);
Insert into Article (Anr, Price) values(4,340.0);
Insert into Article (Anr, Price) values(5,340.0);
Insert into Article (Anr, Price) values(3,200.0);

-- articles for order with orderNr '1'
Insert into Article (Anr, Price) values(2,340.0);
Insert into Article (Anr, Price) values(6,620.0);

-- insert test order that contains the two articles
Insert into Orders (OrderNr) values (1);
Insert into OrderItems(OrderNr, ItemNr, Artnr, Amount) values(1,1,2,4);
Insert into OrderItems(OrderNr, ItemNr, Artnr, Amount) values(1,2,6,2);

我还阅读了主题子查询SQL中的“选择最大值”，但我认为在这种情况下，必须有一种更短的选择方法。

蒂姆·比格莱森（Tim Biegeleisen）

这是一个避免使用您拥有的相关子查询之一的解决方案，而是将其替换为LEFT JOIN：

SELECT a.*
FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
WHERE o.artnr IS NULL AND
    a.price = (SELECT MAX(a.price)
               FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
               WHERE o.artnr IS NULL)

此解决方案应符合ANSI-92，这意味着它应与MySQL，Oracle，SQL Server以及您可能遇到的任何其他类型的快餐友好兼容。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。