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 选项
