5.4 用子表过滤主表

SQL

SELECT City,sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID=Customers.CustomerID
WHERE Orders2021.Quantity>=5
GROUP BY City

SPL
当过滤条件在子表上,且子表过滤后订单记录数较少时,可以用子表过滤主表的方式,其原理和主表过滤子表差不多,区别仅在于过滤子表的同时需要按主键聚合。

A
1 =file(“Orders2021_Account.ctx”).open().cursor@x(CustomerID,Amount;Quantity>=5)
2 =file(“Customers.ctx”).open()
3 =A2.new(A1, sum(Amount):Amount)
4 =A3.groups(City;sum(Amount):Amount)
5 =A2.close()

A1 读子表文件,产生游标,游标中过滤
A2-A3 读主表文件,用 new 关联子表游标。关联时按子表的主键过滤,引用子表的字段同时汇总,返回游标


SQL

SELECT Region,ProductID,sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID=Customers.CustomerID
WHERE Orders2021.Quantity>=5
GROUP BY Region, ProductID

SPL

A
1 =file(“Orders2021_Account.ctx”).open().cursor@x(CustomerID,ProductID,Amount;Quantity>=5)
2 =file(“Customers.ctx”).open()
3 =A2.new@r(A1,Region,ProductID, Amount)
4 =A3.groups(Region, ProductID;sum(Amount):Amount)
5 =A2.close()

A2-A3 由于需要按子表的 ProductID 字段分组汇总,所以和主表关联的时候不能按关联字段聚合,只能关联后再聚合。此时采用 @r 选项