将SQL查询转换为使用集合运算符 [英] Convert SQL Query To Use Set Operators

问题描述

我需要使用Set Operator来完成SQL查询(Oracle)，但我仍在努力了解如何实现它.

I need to complete a SQL Query (Oracle) using a Set Operator, but I am struggling to understand how it would be implemented.

模式:

Customer (ID, firstName, lastName)       - PrimaryKey: ID
Transaction (code, type, date, amount)   - PrimaryKey: code 
performs(code*, ID*)                     - PrimaryKey: code - ForeignKey: ID

查询需要显示Customer.ID的人已经完成了一笔或更少的交易.我可以使用以下命令完成此操作:

The query needs to display the Customer.ID of people have have complete one or less transactions. I can complete this by using the following:

SELECT C.ID
FROM CUSTOMER C
INNER JOIN PERFORMS P
ON C.ID = P.ID
INNER JOIN TRANSACTION T
ON T.CODE = P.CODE
GROUP BY C.ID
HAVING COUNT(T.CODE) <= 1;

但是，我正在努力研究如何使用Set Operator实现同一件事.我相信我应该使用INTERSECT.

But, I am struggling to work out how to use a Set Operator to achieve the same thing. I believe I should be using INTERSECT.

任何帮助将不胜感激！

推荐答案

通常不会这样做，但是作为练习，您可以获取所有客户ID的集合，并减去所有客户ID的集合多于两个的交易，您无需再次查看客户表即可获得:

It's not how you'd normally do this, but as an exercise, you can get the set of all customer IDs, and MINUS the set of all IDs with more than two transactions, which you can get without looking at the customer table a second time:

SELECT C.ID
FROM CUSTOMER C
MINUS
SELECT P.ID
FROM PERFORMS P
INNER JOIN TRANSACTION T
ON T.CODE = P.CODE
GROUP BY P.ID
HAVING COUNT(T.CODE) > 1;

您实际上并不需要加入TRANSACTION，只需直接在PERFORMS中计算代码值即可:

You don't really need to join to TRANSACTION, you can just count the code values in PERFORMS directly:

SELECT C.ID
FROM CUSTOMER C
MINUS
SELECT P.ID
FROM PERFORMS P
GROUP BY P.ID
HAVING COUNT(P.CODE) > 1;

...但是也许您所显示的问题还有更多问题，例如按类型或日期进行限制.

... but maybe there's more to the question what you've shown, like restricting by type or date.

这篇关于将SQL查询转换为使用集合运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！