Vertical merging of query result sets by multiple SQL statements

First, let's look at a demand.

You have a table user table A and a user detail table B and a sales order form C

A---->b one-to-many relationship a---->c one-to-many relationship

If you select a Join B on (foreign key of B and A) query out 2 result sets

If you select a Join C on (foreign key of C and a) query out 3 result sets

That you if you select a Join B on (foreign key of B and a) Join C on (foreign key of C and a) query out 2*3=6 record result set

This time if there is an aggregate function (sum count) in the SELECT statement, it is likely that the result of the query does not meet the requirements of the requirement.

Solutions

1. Use two SQL queries and then merge the result set through Java (two records are queried for the two list).

Cons: If the requirement contains pagination and search ... Not in Java.

2. Use two SQL queries and then merge vertically with SQL

SELECT * FROM (SQL1) alias 1 join (SQL2) alias 2 on ( the condition you need ) is to use two SQL as two sub-query

Advantages: Solve the use of Java Operation List to merge, do not page search problems.

I found it when I was working on a report. When a SQL statement contains multiple one-to-many relationships, pay attention to the results of the aggregation function. So we used to split the SQL first and then merge vertically according to the requirements.

Vertical merging of query result sets by multiple SQL statements