5.3 用主表过滤子表
SQL
SELECT City,ProductID, sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID= Customers.CustomerID
WHERE Customers.Region='Sp'
GROUP BY City,ProductID
SPL
当过滤后的主表记录较少时，可以采取主表过滤子表的做法，即先过滤主表，然后用主表的主键在子表中快速定位并关联输出游标。
| A | |
|---|---|
| 1 | =file(“Customers.ctx”).open().cursor@x(CustomerID, City; Region==“SP”) |
| 2 | =file(“Orders2021_Account.ctx”).open() |
| 3 | =A2.news(A1,Amount,ProductID,City) |
| 4 | =A3.groups(City,ProductID; sum(Amount):Amount) |
| 5 | =A2.close() |
A1 读主表文件，产生游标，游标中过滤
A2-A3 读子表文件，用 news 关联主表游标，关联时按主表的主键过滤，并引用子表的字段 Amount,ProductID,City，返回游标
SQL
SELECT City, sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID= Customers.CustomerID
WHERE Customers.Region='Sp'
GROUP BY City
SPL
| A | |
|---|---|
| 1 | =file(“Customers.ctx”).open().cursor@x(CustomerID, City; Region==“SP”) |
| 2 | =file(“Orders2021_Account.ctx”).open() |
| 3 | =A2.news@r(A1,sum(Amount):Amount,City) |
| 4 | =A3.groups(City; sum(Amount):Amount) |
| 5 | =A2.close() |
A2-A3 本例只需要按 City 聚合，而 City 是由 CustomerID 决定的，所以关联的时候可以用 @r 选项先聚合子表，这样可以少产生一些记录
