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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.