如何在UNION ALL中使用MySQL UNION

I am trying to execute this query:

我正在尝试执行此查询:

(
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (31, 791, 360, 382)
GROUP BY offerId
UNION
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (869, 376, 201, 1246)
GROUP BY offerId
ORDER BY rank DESC
)
UNION ALL
(
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (117, 168, 193, 204, 330, 377, 378, 379, 380, 381, 452, 931, 980, 1100, 1146, 1147, 1190, 1247)
GROUP BY offerId
ORDER BY rank DESC
)

But I get error:

但我得到错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION

您的SQL语法有错误;检查与您的MySQL服务器版本对应的手册,以便在'UNION附近使用正确的语法

So, I guess that either I am using wrong syntax or I am trying to do something that can not be done. I guess that SQL fiddle is not needed for this, most likely experienced people will see right away what is wrong.

所以,我想我要么使用错误的语法,要么我正在尝试做一些无法做到的事情。我想这不需要SQL小提琴,很可能有经验的人会马上看到什么是错的。

Can someone help ? Thanks

有人可以帮忙吗?谢谢

2 个解决方案

#1

Your error is coming from your parentheses. Here's one way to rewrite your current query:

您的错误来自您的括号。这是重写当前查询的一种方法:

SELECT offerId, title, details, link, reference, rank
FROM
(
   SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
   FROM club_offer co
   WHERE co.id IN (31, 791, 360, 382)
   UNION
   SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
   FROM club_offer co
   WHERE co.id IN (869, 376, 201, 1246)
) t
UNION ALL
SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank
FROM club_offer co
WHERE co.id IN (117, 168, 193, 204, 330, 377, 378, 379, 380, 381, 
                452, 931, 980, 1100, 1146, 1147, 1190, 1247)
ORDER BY rank DESC

Condensed Fiddle Demo

浓缩小提琴演示

I've removed the group by and order by clauses from the inner queries as well -- feel free to add back as needed, but the order by in the subquery would be useless.

我已经删除了内部查询中的子组并按子句排序 - 可以根据需要随意添加,但子查询中的顺序将是无用的。

#2

You are using parentesis in a wrong way: the way you put in your code seems that you are trying to establish precedence order between selects unioned statmets, and that is not allowed by the syntax. But as was mention by some one on the coments, you can:

您正在以错误的方式使用parentesis:您放入代码的方式似乎是您尝试在选择联合状态之间建立优先顺序,并且语法不允许这样做。但正如有人对这些内容所提到的那样,你可以:

select * from
    (select ... from ...
    union
    select ... from ...) as T1
union all
    select ... from ...

Where all sides of union/union all are single queryes.

联盟/联盟的所有方面都是单一查询。

Some minor issues:
order by inside each select has no sense if no limit clause is present.
not in all cases group by (using mysql hability to miss grouping functions) results the same result that distinct

一些小问题:如果没有限制条款,每个选择内部的顺序是没有意义的。并非在所有情况下group by(使用mysql hability错过分组函数)会产生相同的结果

#1