主页 / 问题 / 955904
Accepted
Леонид Дубравский
Asked:2020-03-13 18:28:01 +0000 UTC2020-03-13 18:28:01 +0000 UTC

如何正确使用 UNION ALL 和 order by？

  • 772

我正在尝试合并几个查询：

          SELECT 'object1', MessageNo, _Order FROM _Object1Changes WHERE MessageNo=0 AND Node='33' order by _Order desc  LIMIT 1
UNION ALL SELECT 'object2', MessageNo, _Order FROM _Object2Changes WHERE MessageNo=0 AND Node='33' order by _Order desc  LIMIT 1
UNION ALL SELECT 'object3', MessageNo, _Order FROM _Object3Changes WHERE MessageNo=0 AND Node='33' order by _Order desc  LIMIT 1
UNION ALL SELECT 'object4', MessageNo, _Order FROM _Object4Changes WHERE MessageNo=0 AND Node='33' order by _Order desc  LIMIT 1;

错误代码：1221。UNION 和 ORDER BY 的使用不正确

或者我尝试这样：

          SELECT 'object1', MessageNo, MIN(_Order) FROM _Object1Changes WHERE MessageNo=0 AND Node='33' LIMIT 1
UNION ALL SELECT 'object2', MessageNo, MIN(_Order) FROM _Object2Changes WHERE MessageNo=0 AND Node='33'  LIMIT 1
UNION ALL SELECT 'object3', MessageNo, MIN(_Order) FROM _Object3Changes WHERE MessageNo=0 AND Node='33'LIMIT 1
UNION ALL SELECT 'object4', MessageNo, MIN(_Order) FROM _Object4Changes WHERE MessageNo=0 AND Node='33' LIMIT 1;

我得到一张有一条记录的表，'object1'，null，null。目标是从多个表中获取结果，其中从每个表中获取最小值为 _Order 和 Node='33' 的记录

如何解决这个问题？

mysql

1 个回答

  1. Best Answer

    从UNION Syntax文档章节：

    要将 ORDER BY 或 LIMIT 应用于单个 SELECT，请将子句放在括住 SELECT 的括号内

    将各个子查询括在括号中：

    (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
    • 1

相关问题