1: 什么时候用union和union all ?
我们经常会碰到这样的应用,两个表的数据按照一定的查询条件查询出来以后,需要将结果合并到一起显示出来,这个时候就需要用到union和union all关键字来实现这样的功能,union和union all的主要区别是union all是把结果集直接合并在一起,而union 是将union all后的结果镜像一次distinct,去除重复的记录后的结果。
看下面的例子:
查询一:
查询二:
两个结果集进行union all为(直接将两个查询结果集合并):
两个结果集镜像union 运算结果为(两个结果集合并后进行去重/distinct):
当使用 UNION 时,MySQL 会把结果集中重复的记录删掉,而使用 UNION ALL ,MySQL 会把所有的记录返回,且效率高于 UNION。
总结:在xwwd项目中由于还款计划表(tb_laon_repayment)数据量巨大(数量2000万),
查询一条借款的还款计划时候巨慢,当时为了解决查询慢问题就建立了一张还款计划
备份表(tb_loan_repayment_his),将还款计划表tb_loan_repayment中的已经结清
的借款的还款计划数据导出1000万到了还款计划备份表tb_loan_repayment_his中,
这样做解决了还款计划表tb_loan_repayment中数据过大导致查看借款loan详情时展示
查询该笔借款loan的还款计划查询卡死的情况。 后来由于项目需要需要查询备份表中的数据,
于是对内管查看借款详情接口进行了优化升级。 获取具体某一条还款计划的时候首先查询
还款计划表tb_laon_repayment如果没有再去查询还款计划备份表tb_loan_repayment_his.
同时“借款管理”菜单中是去分页查询所有还款计划,为了数据完整性,
这里就采用了union联合查询tb_loan_repayment,和tb_tb_repayment_his表。
其他练习:
student表:
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP
TABLE
IF EXISTS `student`;
CREATE
TABLE
`student` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`
name
`
varchar
(10)
DEFAULT
NULL
,
`age` tinyint(4)
DEFAULT
NULL
,
`classId`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5
DEFAULT
CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT
INTO
`student`
VALUES
(
'1'
,
's1'
,
'20'
,
'1'
);
INSERT
INTO
`student`
VALUES
(
'2'
,
's2'
,
'22'
,
'1'
);
INSERT
INTO
`student`
VALUES
(
'3'
,
's3'
,
'22'
,
'2'
);
INSERT
INTO
`student`
VALUES
(
'4'
,
's4'
,
'25'
,
'2'
);
teacher表:
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP
TABLE
IF EXISTS `teacher`;
CREATE
TABLE
`teacher` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`
name
`
varchar
(10)
DEFAULT
NULL
,
`age` tinyint(4)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4
DEFAULT
CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT
INTO
`teacher`
VALUES
(
'1'
,
't1'
,
'36'
);
INSERT
INTO
`teacher`
VALUES
(
'2'
,
't2'
,
'33'
);
INSERT
INTO
`teacher`
VALUES
(
'3'
,
's3'
,
'22'
);
查询数据如下:
mysql>
SELECT
*
FROM
student;
+
----+------+-----+---------+
| id |
name
| age | classId |
+
----+------+-----+---------+
| 1 | s1 | 20 | 1 |
| 2 | s2 | 22 | 1 |
| 3 | s3 | 22 | 2 |
| 4 | s4 | 25 | 2 |
+
----+------+-----+---------+
4
rows
in
set
mysql>
SELECT
*
FROM
teacher;
+
----+------+-----+
| id |
name
| age |
+
----+------+-----+
| 1 | t1 | 36 |
| 2 | t2 | 33 |
| 3 | s3 | 22 |
+
----+------+-----+
3
rows
in
set
使用 UNION的结果:
mysql>
SELECT
id,
name
, age
FROM
student
->
UNION
-- 与UNION DISTINCT相同
->
SELECT
id,
name
, age
FROM
teacher;
+
----+------+-----+
| id |
name
| age |
+
----+------+-----+
| 1 | s1 | 20 |
| 2 | s2 | 22 |
| 3 | s3 | 22 |
| 4 | s4 | 25 |
| 1 | t1 | 36 |
| 2 | t2 | 33 |
+
----+------+-----+
6
rows
in
set
使用 UNION ALL的结果:
mysql>
SELECT
id,
name
, age
FROM
student
->
UNION
ALL
->
SELECT
id,
name
, age
FROM
teacher;
+
----+------+-----+
| id |
name
| age |
+
----+------+-----+
| 1 | s1 | 20 |
| 2 | s2 | 22 |
| 3 | s3 | 22 |
| 4 | s4 | 25 |
| 1 | t1 | 36 |
| 2 | t2 | 33 |
| 3 | s3 | 22 |
+
----+------+-----+
7
rows
in
set
其实联合查询跟字段的类型无关,只要求每个SELECT查询的字段数一样,能对应即可,如:
mysql>
SELECT
id,
name
, age
FROM
student
-- 这里可以看出第一个SELECT语句中的字段名称被用作最后结果的字段名
->
UNION
->
SELECT
age,
name
, id
FROM
teacher;
+
----+------+-----+
| id |
name
| age |
+
----+------+-----+
| 1 | s1 | 20 |
| 2 | s2 | 22 |
| 3 | s3 | 22 |
| 4 | s4 | 25 |
| 36 | t1 | 1 |
| 33 | t2 | 2 |
| 22 | s3 | 3 |
+
----+------+-----+
7
rows
in
set
在联合查询中,当使用ORDER BY的时候,需要对SELECT语句添加括号,并且与LIMIT结合使用才生效,如:
mysql> (
SELECT
classId, id,
name
, age
FROM
student
WHERE
classId = 1
ORDER
BY
age
DESC
)
->
UNION
-> (
SELECT
classId, id,
name
, age
FROM
student
WHERE
classId = 2
ORDER
BY
age);
+
---------+----+------+-----+
| classId | id |
name
| age |
+
---------+----+------+-----+
| 1 | 1 | s1 | 20 |
| 1 | 2 | s2 | 22 |
| 2 | 3 | s3 | 22 |
| 2 | 4 | s4 | 25 |
+
---------+----+------+-----+
4
rows
in
set
此时classId为1的学生并没有按照年龄进行降序,结合LIMIT后:
mysql> (
SELECT
classId, id,
name
, age
FROM
student
WHERE
classId = 1
ORDER
BY
age
DESC
LIMIT 2)
->
UNION
-> (
SELECT
classId, id,
name
, age
FROM
student
WHERE
classId = 2
ORDER
BY
age);
+
---------+----+------+-----+
| classId | id |
name
| age |
+
---------+----+------+-----+
| 1 | 2 | s2 | 22 |
| 1 | 1 | s1 | 20 |
| 2 | 3 | s3 | 22 |
| 2 | 4 | s4 | 25 |
+
---------+----+------+-----+
4
rows
in
set
转载链接:
/ganbo/p/10803786.html
/huangminwen/p/9261298.html