Existence judgment in structured data

2020-11-06 01:16:08 raqsoft

【 Abstract 】
When selecting data from a data table , Sometimes it is necessary to judge whether members exist under certain conditions , These conditions may be queried from other data tables . For example, a score is a field in a grade sheet , How to choose from the student list that each subject score is higher than 80 The students of grade ？ How to deal with the problem of judging the existence of structured data easily and quickly , Here's the whole process , And provide esProc Sample code . The problem of existence judgment in structured data

1. Detection of the existence of foreign key mappings

In two tables , Find records based on the existence of the foreign key mapping .

【 example 1】 Count the average score of boys in class one . The grade and student charts are as follows ：

【 Their thinking 】

When selecting data from the score table , Judge whether there is a record that the class name is a class and the student's gender is male , If there is, choose .

【SPL Script 】

A6 The results are as follows ：

When the foreign key table has a large amount of data , You can use an ordered merge of cursors to solve .

【 example 2】 Inquire about 2014 The number of orders without discount per month . The order list and order details are as follows ：

【 Their thinking 】

When selecting data from the order form , Judge whether there is discount for 0 The order of , If there is, choose .

【SPL Script 】

A6 The results are as follows ：

2. Detection of the existence of non equivalent connections

In a table , Find data by detecting the existence of non equivalent links .

【 example 3】 Query the sales volume of orders with the same order span more than one year . Part of the order form is as follows ：

【 Their thinking 】

When selecting data from the order table , Judge whether the records with order span more than one year exist , If there is, choose .

【SPL Script 】

A5 The results are as follows ：

3. Nonexistence detection of foreign key mapping

In two tables , Find records based on nonexistence detection of foreign key mapping .

【 example 4】 All subjects are higher than 80 The students of grade . The grade and student charts are as follows ：

【 Their thinking 】

When selecting data from the student table , Judge whether any subject is lower than 80 Points of , If not, choose .

【SPL Script 】

A6 The results are as follows ：

4. The existence test of double negation

Through double negation , Query records that can match .

【 example 5】 Check the students who have taken all courses . Course selection table 、 The curriculum and student schedule are as follows ：

【 Their thinking 】

When selecting data from the student table , Judge whether the student has a record that is not selected in the course of a certain subject , If not, choose . When dealing with the existence detection of double negation , We can also think positively , Just select a record with the same number of elective subjects as all subjects .

【SPL Script 】

A7 The results are as follows ：

5. Detection of the existence of arbitrary conditions

In two tables , Find records according to the existence of any condition .

【 example 6】 The score difference between the two subjects is more than 30 The students of grade . The grade and student charts are as follows ：

【SQL sentence 】

When selecting data from the student table , Judge whether there is any difference between the scores of any two subjects 30 A minute's record , Choose if you exist .

【SPL Script 】

A7 The results are as follows ：

6. Detection of the existence of all conditions

According to the data in a table , Filter out records that meet all criteria .

【 example 7】 Find out which employees are paid more than all sales employees . Some of the data in the employee table are as follows ：

【SQL sentence 】

When selecting data from the employee table , Judge whether there is a record that the employee's salary is greater than that of all employees in the sales department , Choose if you exist .

【SPL Script 】

A4 The results are as follows ：

SPL CookBook》 There are more examples of relevant calculations in .