"SQL SELECT Distinct CustomerID FROM Orders2020 UNION SELECT Distinct CustomerID FROM Orders2021 .."

Mars 开封
乾学院 6 号会员
494 浏览 • 1 年前

5.2 计算交并集

计算＆AI

SQL

SELECT Distinct CustomerID
FROM Orders2020
UNION
SELECT Distinct CustomerID
FROM Orders2021

SPL

	A
1	=file(“Orders2020_Account.ctx”).open().cursor@x(CustomerID).group@s(CustomerID;)
2	=file(“Orders2021_Account.ctx”).open().cursor@x(CustomerID).group@s(CustomerID;)
3	=[A1,A2].mergex@u(CustomerID)

A1-A2 从按主键有序的组表文件中分别读出两个年份的数据，并聚合。注意：没有聚合表达式的时候，如果要聚合必须在参数末尾加上分号
A3 用 mergex 函数采用有序归并的方式合并两个游标，返回游标，后续根据需要从游标读数。@u 表示并集

SQL

SELECT Distinct CustomerID
FROM Orders2020
INTERSECT
SELECT Distinct CustomerID
FROM Orders2021

SPL

	A
1	=file(“Orders2020_Account.ctx”).open().cursor@x(CustomerID).group@s(CustomerID;)
2	=file(“Orders2021_Account.ctx”).open().cursor@x(CustomerID).group@s(CustomerID;)
3	=[A1,A2].mergex@i(CustomerID)

SQL

SELECT DISTINCT CustomerID
FROM Orders2020
EXCEPT                        --MINUS
SELECT Distinct CustomerID
FROM Orders2021

SPL

	A
1	=file(“Orders2020_Account.ctx”).open().cursor@x(CustomerID).group@s(CustomerID;)
2	=file(“Orders2021_Account.ctx”).open().cursor@x(CustomerID).group@s(CustomerID;)
3	=[A1,A2].mergex@d(CustomerID)