不使用子查询（在HAVING子句中的子查询）重写SQL查询

Question

在我的期末考试中，我遇到了相当棘手的SQL、关系代数和关系演算查询。其中有一个查询如下所示：

找出所有从类别“计算机”中订购所有产品的客户的姓名。 （即订购了计算机类别中每一种产品的客户）

以下是模式：

Customer (Customer_Id, Cust_First_Name, Cust_Last_Name)

Orders (Order_Id, Customer_Id)

Order_items (Order_Item_id, Order_Id, Product_Id)

Product_info (Product_Id, Product_Name, Category)

粗体表示主键，斜体表示外键。

现在，为了将此查询转换为关系代数，我需要使用连接而不是子查询。为了帮助自己，我首先编写SQL，然后将SQL查询转换为关系代数。

以下是我的尝试：

尝试1（使用子查询）：

select C.Customer_Id
from Customer C
where
(
select count(*)
from product_info
where category = 'Computer'
)=(select count (distinct pi.Product_id)
from orders S, order_items OI, product_info pi
where S.Customer_Id = C.Customer_Id and S.Order_Id = OI.Order_Id and  pi.product_id=OI.product_id and category = 'Computer')

尝试方法2（在having子句中使用一个子查询）：

select C.Customer_Id
from Customer C, Product_info pi, Orders S, Order_Items oi
where C.Customer_Id = S.Customer_Id and S.Order_Id = OI.Order_Id and OI.Product_Id = pi.Product_Id and pi.category = 'Computer'
group by C.Customer_Id
having count (distinct pi.Product_Id) = 
(
select count (*) 
from Product_info
where category = 'Computer'
)

尝试3（使用FROM子查询）：

select C.Customer_Id
from Customer C, Product_info pi, Orders S, Order_Items oi,
(
select count (*) num
from Product_info
where category = 'Computer'
) numbr
where C.Customer_Id = S.Customer_Id and S.Order_Id = OI.Order_Id and OI.Product_Id = pi.Product_Id and pi.category = 'Computer'
group by C.Customer_Id, numbr.num
having count (distinct pi.Product_Id) = numbr.num

现在，这个查询可以用关系代数来表示，但是效率很低，因为它会重复值。

我的最后一次尝试（无法编译且使用了where子查询）：

select *
from Customer C
where not exists
(select *
from (select Order_Id from orders O where O.Customer_Id = C.Customer_Id) S INNER JOIN order_items OI on S.Order_Id = OI.Order_Id
RIGHT OUTER JOIN (select Product_Id from product_info where category ='Computer') PI on PI.Product_Id = OI.Product_Id
where OI.Product_Id = null)

我在某处看到说，在这种情况下可以使用LATERAL，但是关于LATERAL的信息太少了，我无法正确理解它。

考试已经结束，但我仍对解决方案感兴趣。因为这是一个有6个查询、ER图、ER转关系、BCNF规范化和3NF规范化的2小时考试，我想知道为什么这些查询会如此难以解决。我是否遗漏了什么关键信息？

以下是一小部分数据，帮助我稍微理解一下：

http://pastebin.com/DkCe0AGm

提前感谢。

- greensquare

---

你可以在关系代数中使用除法吗？ - Jodaka

Answer 1

这在关系代数中非常简单，只需使用除法运算符。需要注意的是，虽然你可以用关系代数编写的任何内容都可以用SQL编写，但并不意味着你可以以相同的方式在SQL中编写关系代数的所有内容。SQL没有与除法运算符易于对应的运算符，因此首先在SQL中尝试编写这个操作并不会有所帮助。

由于我不知道如何在这里输入希腊字母，我将把一些东西写出来。

Sigma -> SELECT
Pi -> PROJECT
Rho -> RENAME

PROJECT c.Cust_First_Name, c.Cust_Last_Name, i.Product_ID (SELECT c.customer_id = o.customer_id, o.order_id = i.order_id (RENAME (Customer c) X RENAME (Orders o) X RENAME (Order_items i))) 
DIVIDE PROJECT p.product_id (SELECT p.category = 'Computers' (RENAME (Products p)))

如果您在LaTeX编辑器中输入此内容，您将看到其实际形式：

\Pi_{c.cust\_last\_name, c.cust\_first\_name, i.product\_id} (\sigma_{c.customer\_id = o.customer\_id, o.order\_id = i.order\_id}(\rho_{c}(customer) X \rho_{o}(orders) X \rho_{i}(order\_items))) 
\div  \Pi_{p.product\_id}(\sigma_{p.category='computers'}(\rho_{p}(products)))

您可以或许认为这是一个子查询，但我认为这只是两个不同的查询。

- Jodaka

---

哇，非常感谢你提供的除法运算符。那正是我所缺失的关键环节。 - greensquare

你说得对，我错了，试图找到将SQL查询转换为关系代数的算法。我在一个小例子上尝试了一下，确实找到了以下等价于除法运算符的方法：http://www.freeimagehosting.net/fdn5p - greensquare

很高兴能帮忙！另外，记住每当你在StackOverflow上提问时，如果有人提供了令人满意的答案，请点赞并标记为正确答案 - 这不仅有助于回答者，也有助于其他查看你问题的人。 - Jodaka

我确实尝试给你的帖子点赞并标记为正确答案，但当我点击箭头时，它显示：“点赞需要15个声望”，而我只有11个声望。对此我感到抱歉。祝好。 - greensquare

每次您标记答案为正确，都会获得2个声望。 - Jodaka

Answer 2

我觉得这个问题有歧义。这个版本可以获取只从计算机类别订购产品的客户：

select c.customer_id, c.Cust_First_Name, c.Cust_Last_Name
from Customer c join
     Orders o
     on c.customer_id = o.customer_id join
     Order_Item oi
     on o.order_Id = oi.order_id join
     Product_Info pi
     on oi.Product_id = pi.product_id
group by c.customer_id, c.Cust_First_Name, c.Cust_Last_Name
having min(case when pi.category = 'Computer' then 1 else 0 end) = 1

在这种情况下，我只是在计算客户是否有任何未购买的类别产品。

另一种解释是“订购了计算机类别中的每个产品的客户”：

select c.customer_id, c.Cust_First_Name, c.Cust_Last_Name
from Customer c join
     Orders o
     on c.customer_id = o.customer_id join
     Order_Item oi
     on o.order_Id = oi.order_id join
     Product_Info pi
     on pi.Product_id = oi.Product_id cross join
     (select count(distinct product_id) as cnt
      from Product_info pi
      where category = 'Computer'
     ) comps
where pi.Category = 'Computer'
group by c.customer_id, c.Cust_First_Name, c.Cust_Last_Name
having count(distinct product_id) = comps.cnt

在这种情况下，想法是计算不同产品的数量并查看计数是否匹配。

我不确定将它们翻译成关系代数是否有助于形成良好的查询。

- Gordon Linoff

---

谢谢回复。实际上，您的查询与我在尝试3中的查询等效。它可以形成良好的关系代数查询，但不幸的是它是低效的，因为它涉及冗余。 - greensquare