“union”或附近的SQL语法错误

I have a small query, and a union to put another small query next to it. However, the union has a syntax error in it.

我有一个小查询，并在其旁边放置另一个小查询的联合。但是，联合中有一个语法错误。

Select <column1>
      ,<column2>
From <Table1> 
<Some joins in there>
Where <conditions>
group by <column2>
order by <column2>

union

select <column2>
      ,<column3>
      ,<column4>
From <Table2>
<Some more joins here>
Where <conditions>
group by <column2>
order by <column2>

This is the Error I receive

这是我收到的错误

ERROR: Syntax error at or near 'union'

2 个解决方案

#1

I see what was wrong. You have to place the order by at the end of the query, and only at the end. It gave me an error because it thought the query had eneded.

我看到了什么是错的。您必须在查询结束时放置订单，并且仅在结尾处放置订单。它给了我一个错误，因为它认为查询已经消失了。

Select <column1>
      ,<column2>
      ,<aggregate column3>
From <Table1> 
<Some joins in there>
Where <conditions>
group by <column2>, <column1>

union

select <column2>
      ,<column3>
      ,<aggregate column4>
From <Table2>
<Some more joins here>
Where <conditions>
group by <column2>, <column3>
order by <column2>

That did the trick.

这就是诀窍。

#2

Short answer: (SELECT... ORDER BY..) UNION (SELECT .. ORDER BY...) does work.

简答：（SELECT ... ORDER BY ..）UNION（SELECT .. ORDER BY ...）确实有效。

See the documentation about UNION:

请参阅有关UNION的文档：

UNION Clause

UNION条款

The UNION clause has this general form:

UNION子句具有以下一般形式：

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement UNION [ALL | DISTINCT] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

select_statement是没有ORDER BY，LIMIT，FOR NO KEY UPDATE，FOR UPDATE，FOR SHARE或FOR KEY SHARE子句的任何SELECT语句。（ORDER BY和LIMIT可以附加到子表达式中，如果它括在括号中。没有括号，这些子句将被用于应用于UNION的结果，而不是它的右侧输入表达式。）

#1