MySQL之SQL语句实现将三个查询结果集拼接成一个表

遇到的问题：

现在利用SQL语句查询得到三个结果集，每一个结果集是两列，具体为排名序号与对应的名字，如下表一。但是现在需要将这三个结果集拼接成为一个表，并且只有一个排名序号。

排名	姓名
1	张三
2	李四

 

 

 

利用SQL语句实现：

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  //这里的两个单引号分别代表两个空值，也就是把post1与post2值设为空，最后也就是把第一列与第二列的设为空的一列。
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

注：

1.这里面涉及四个表exam_main a,  exam_rel_user b,  account c,  core_dept d ，其中考试记录表exam_main中的id为考试结果表exam_rel_user的exam_id外键，考试结果表中user_id外键为人员表中user_id，人员表中dept_id外键为core_dept部门表中的id。

2.每个结果集看作是一个表，三个结果集用 union all 连接，最后将三个结果集又看做一个表。

3.用SQL语句产生排名序号利用的是在结果集（当做一个表）之外添加查询.如下：

 SELECT @rownum := @rownum + 1 AS rownum,'' as post1, '' as post2, firstname as post3
 FROM   （结果集）aa

(SELECT @rownum3 := 0) bb 

此处勿忘在最后的括号外添加别名，否则会出错。

 

转载于:https://www.cnblogs.com/Klose-AK47/p/5799618.html