我有一个表
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)
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/113604.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...