Mysql遇到错误总结

最新推荐文章于 2021-02-27 00:31:19 发布

娜些时光(*^▽^*)

最新推荐文章于 2021-02-27 00:31:19 发布

阅读量162

点赞数

分类专栏： sql 文章标签： sql

本文链接：https://blog.csdn.net/qq_35822148/article/details/84527241

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在我同时使用order by 和union 时报错Incorrect usage of UNION and ORDER BY

展示错误sql

SELECT ilcd.rfidStatus,ilcd.rfidSN as rfieSn from i_locker_communication ilc
LEFT JOIN i_locker_communication_detail ilcd on ilcd.commId = ilc.commId
WHERE ilcd.rfidSN = '1'
ORDER BY ilc.createTime DESC LIMIT 1
UNION
SELECT ilcd.rfidStatus,ilcd.rfidSN as rfieSn from i_locker_communication ilc
LEFT JOIN i_locker_communication_detail ilcd on ilcd.commId = ilc.commId
WHERE ilcd.rfidSN = '2'
ORDER BY ilc.createTime DESC LIMIT 1

此时在运行sql的时候报错Incorrect usage of UNION and ORDER BY

后来查了一番资料在同时使用union和order by 是需要对每个子句用括号括起来

正确sql

(SELECT ilcd.rfidStatus,ilcd.rfidSN as rfieSn from i_locker_communication ilc
LEFT JOIN i_locker_communication_detail ilcd on ilcd.commId = ilc.commId
WHERE ilcd.rfidSN = '1'
ORDER BY ilc.createTime DESC LIMIT 1)
UNION
(SELECT ilcd.rfidStatus,ilcd.rfidSN as rfieSn from i_locker_communication ilc
LEFT JOIN i_locker_communication_detail ilcd on ilcd.commId = ilc.commId
WHERE ilcd.rfidSN = '2'
ORDER BY ilc.createTime DESC LIMIT 1)

这样才能使用union 查询出来一个临时表在对其进行其他查询操作