12.11 Judge existence of data matching
Based on association of two tables, get records through judging existence of data matching.
According to Student table and Score table, find students who have two subjects whose score difference is greater than 30.
| Student |
|---|
| ID |
| Class |
| Name |
| … |
| Score |
|---|
| StudentID |
| Subject |
| Score |
| … |
We just need to check whether the difference between a student’s highest score and lowest score is greater than 30.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Student”) |
| 3 | =A1.query@x(“select * from Score”) |
| 4 | =A3.group(StudentID) |
| 5 | =A4.select(~.max(Score)-~.min(Score)>30) |
| 6 | =A5.id(StudentID) |
| 7 | =A2.join@i(ID,A6) |
A1 Connect to the database.
A2 Query Student table.
A3 Query Score table.
A4 Group Score table by StudentID.
A5 Find students whose highest score and lowest has a difference greater than 30.
A6 Perform distinct on StudentID.
A7 The A.join@i() function performs filtering join.
Execution result:
| ID | Name | Class |
|---|---|---|
| 4 | Emily Smith | Class 1 |
| 8 | Megan | Class 1 |
| … | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
