mysql中的union和order by limit

首先给大家推荐一下我老师大神的人工智能教学网站。教学不仅零基础,通俗易懂,而且非常风趣幽默,还时不时有内涵黄段子!点这里可以跳转到网站

我有一个表

CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `desc` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(1)以下查询会报错误:[Err] 1221 – Incorrect usage of UNION and ORDER BY

select * from test1 where name like ‘A%’ order by name
union
select * from test1 where name like ‘B%’ order by name

应改为:

select * from test1 where name like ‘A%’
union
select * from test1 where name like ‘B%’ order by name

因为union中,在不用括号的情况下,只能用一个order by(想一想,如果union两边的order by的列名不一样会怎么样),这会对union后的结果集进行排序

或者改为:

(select * from test1 where name like ‘A%’ order by name)
union
(select * from test1 where name like ‘B%’ order by name)

这两个order by在union前进行

(2)同样的

select * from test1 where name like ‘A%’ limit 10
union
select * from test1 where name like ‘B%’ limit 20

相当于

(select * from test1 where name like ‘A%’ limit 10)
union
(select * from test1 where name like ‘B%’) limit 20

即后一个limit作用于的是union后的结果集,而不是union后的select

也可以加括号来得到你想要的结果

(select * from test1 where name like ‘A%’ limit 10)
union
(select * from test1 where name like ‘B%’ limit 20)

(3)UNION和UNION ALL区别

union会过滤掉union两边的select得到的结果集中的重复的行,而union all不会过滤掉重复的行

点这里可以跳转到人工智能网站

发表评论