SQL运行报错：Incorrect usage of UNION and ORDER BY 问题

炸丶天

于 2023-11-07 10:26:32 发布

阅读量480

点赞数

CC 4.0 BY-SA版权

文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/qq_43356292/article/details/134261319

文章讲述了在SQL查询中，当使用UNION和ORDERBY时遇到错误，原因是没有正确使用括号。解决方案是将每个子查询加上括号后分别排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL运行报错：Incorrect usage of UNION and ORDER BY 问题

执行如下图 sql语句时报错 :Incorrect usage of UNION and ORDER BY



select st1.*,c.c_name ,sc1.s_score from table1 t1 left join table2 t2 on t2.t_id = t1.t_id  order by t2.id desc 
union all
select st1.*,c.c_name ,sc1.s_score from table1 t1 left join table2 t2 on t2.t_id = t1.t_id  order by t2.id desc 
union all
select st1.*,c.c_name ,sc1.s_score from table1 t1 left join table2 t2 on t2.t_id = t1.t_id  order by t2.id desc

是因为UNION 和order by 一起使用时，如果没有括号的情况下，只能使用一个order by。所以加上括号就可以了：

select * from(
select st1.*,c.c_name ,sc1.s_score from table1 t1 left join table2 t2 on t2.t_id = t1.t_id  order by t2.id desc)a 
union all
select * from(
select st1.*,c.c_name ,sc1.s_score from table1 t1 left join table2 t2 on t2.t_id = t1.t_id  order by t2.id desc)b
union all
select * from(
select st1.*,c.c_name ,sc1.s_score from table1 t1 left join table2 t2 on t2.t_id = t1.t_id  order by t2.id desc)c

问题解决。