MySQL SQL statement stitching three query result sets into a single table

Problems encountered:

Now using the SQL statement query to get three result sets, each result set is two columns, specific to the rank ordinal with the corresponding name, such as the following table I. But now you need to stitch these three result sets into a table with only one rank ordinal.

Ranking	Name
1	Tom
2	John doe

Use SQL statements to implement:

SELECT rownum, MAX (post1) as Post1,max (POST2) as Post2,max (POST3) as Post3
From
(SELECT @rownum1: = @rownum1 + 1 as rownum, FirstName as Post1, ' as Post2, ' as Post3
From
(SELECT FirstName
From
Exam_main A,
Exam_rel_user B,
Account C,
Core_dept D
WHERE A. ' id ' = B. ' exam_id '
and B. ' user_id ' = c. ' user_id '
and C. ' dept_id ' = d. ' id '
and C. ' Position ' in (
' 01010101 ',
' 01010102 ',
' 01010104 '
)
ORDER by B.score DESC,
B.last_attempt_date ASC) AA,
(SELECT
@rownum1: = 0) bb

UNION All

SELECT @rownum2: = @rownum2 + 1 as rownum, "as post1,firstname as Post2," as Post3
From
(SELECT
FirstName
From
Exam_main A,
Exam_rel_user B,
Account C,
Core_dept D
WHERE A. ' id ' = B. ' exam_id '
and B. ' user_id ' = c. ' user_id '
and C. ' dept_id ' = d. ' id '
and C. ' Position ' in (
' 01010201 ',
' 01010202 ',
' 01010204 '
)
ORDER by B. ' Score ' DESC,
B. ' last_attempt_date ' ASC) AA,
(SELECT
@rownum2: = 0) bb

UNION ALL

SELECT @rownum3: = @rownum3 + 1 as rownum, "as Post1," as Post2, FirstName as Post3//The two single quotes here represent two null values, that is, set the Post1 and POST2 values to null, and finally, the first column and the second column is set to a blank column.
From
(SELECT
FirstName
From
Exam_main A,
Exam_rel_user B,
Account C,
Core_dept D
WHERE A. ' id ' = B. ' exam_id '
and B. ' user_id ' = c. ' user_id '
and C. ' dept_id ' = d. ' id '
and C. ' Position ' in (' 01010203 ', ' 01010206 ')
ORDER by B. ' Score ' DESC,
B. ' last_attempt_date ' ASC) AA,
(SELECT
@rownum3: = 0) bb

) cc

/* Where rownum = 1*/
GROUP BY RowNum

Note:

1. This involves four tables exam_main A, Exam_rel_user B, account C, core_dept D, where the exam record table exam_main the ID for the test results table Exam_r El_user the exam_id foreign key, the user_id foreign key in the test results table is user_id in the People table, and the dept_id foreign key in the People table is the ID in the Core_dept Department table.

2. each result set is considered to be a table, three result sets are concatenated with union all, and three result sets are considered as a table at the end.

3. Using an SQL statement to generate a rank ordinal takes advantage of adding a query outside the result set (as a table).

SELECT @rownum: = @rownum + 1 as rownum, "as Post1," as Post2, FirstName as Post3
From (Result set) AA

(SELECT @rownum3: = 0) bb

Do not forget to add an alias outside the last parenthesis here, otherwise an error will occur.

MySQL SQL statement stitching three query result sets into a single table