复杂的Oracle SQL查询

debugcn 发表于 Dev

nb97

我几周前就开始学习SQL，遇到了一个我似乎无法解决的有趣问题。这是数据库结构：

Material (name, price, origin_country) 
Product (name, price) 
Quantities (productName, materialName, quantity)

查询应显示价格在20.000或以下的大多数产品中使用的材料的所有数据。

这是我到目前为止所拥有的：

select max(count(m.name))
from Materials m
join Quantities q on (m.name=q.materialName)
join Product p on (p.name=q.productName)
where p.price < 20000
group by m.name

从理论上讲，这应该显示出某种材料出现的最大数量（实际上确实如此）。问题是，我不知道如何以可以向我显示Material表中数据的方式来实现这一点。

MT0

将表连接在一起，筛选出价格过高的产品，然后使用COUNT解析函数查找产品数量并按ORDER BY降序计数并返回FIRST ROW WITH TIES：

SELECT M.*,
       P.name AS ProductName,
       p.Price,
       Q.Quantity,
       COUNT( DISTINCT p.ROWID ) OVER ( PARTITION BY m.ROWID ) AS num_products
FROM   Material M
       INNER JOIN Quantities Q
       ON ( m.Name = Q.MaterialName )
       INNER JOIN Product P
       ON ( Q.ProductName = P.Name )
WHERE  p.price <= 20000
ORDER BY num_products DESC
FETCH FIRST ROW WITH TIES;

（我ROWID在计数中使用了伪列，以防万一有两种具有相同名称的产品或材料，则不会将它们分组在一起。如果不是这种情况，则可以使用p.Name和m.Name代替。）

其中，对于示例数据：

CREATE TABLE Material( name, price, origin_country ) AS
SELECT 'M1', 1, 'Place1' FROM DUAL UNION ALL
SELECT 'M2', 2, 'Place2' FROM DUAL UNION ALL
SELECT 'M3', 3, 'Place3' FROM DUAL;

CREATE TABLE Product (name, price) AS
SELECT 'P123', 10 FROM DUAL UNION ALL
SELECT 'P13',   8 FROM DUAL UNION ALL
SELECT 'P223',  5 FROM DUAL;

CREATE TABLE Quantities (productName, materialName, quantity) AS
SELECT 'P123', 'M1', 1 FROM DUAL UNION ALL
SELECT 'P123', 'M2', 1 FROM DUAL UNION ALL
SELECT 'P123', 'M3', 1 FROM DUAL UNION ALL
SELECT 'P13',  'M1', 1 FROM DUAL UNION ALL
SELECT 'P13',  'M3', 1 FROM DUAL UNION ALL
SELECT 'P223', 'M2', 1 FROM DUAL UNION ALL
SELECT 'P223', 'M2', 1 FROM DUAL UNION ALL
SELECT 'P223', 'M3', 1 FROM DUAL;

输出：

NAME | 价格| ORIGIN_COUNTRY | PRODUCTNAME | 价格| 数量| NUM_PRODUCTS个
：- ----：| ：------------- | ：---------- | ----：| -------：| -----------：
M3 | 3 | Place3 | P123 | 10 | 1 | 3 
M3 | 3 | Place3 | P223 | 5 | 1 | 3 
M3 | 3 | Place3 | P13 | 8 | 1 | 3