Grouping the subtable

List the names of the employees and count the cities where each employee has worked for over one year.

SQL

SELECT name,count(*) city_total
FROM (SELECT employee.name name,resume.city city
    FROM employee,resume WHERE employee.name=resume.name
    GROUP BY name,city
    HAVING sum(work_days)>=365)
GROUP BY name

Process the subtable through the multi-table join. The grouped result set has the same number of records as the subtable has. It must be grouped again in order to join the records to have the same number as the main table has.

SPL

A
1 =demo.query(“select * from employee”).new(name,resume.group(city).count(~.sum(work_days)>=365):city_total)

SPL handles the sets of the subtable as the fields of the main table, hence groups and filters them as a regular set.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.