sql - 如果您采用 SQL 查询并对其自身执行 UNION，什么会导致语法错误？

Question

我在 SQL Server 2005 中遇到了这个奇怪的错误，我在其中进行了一个工作查询，在其下方添加了 UNION 关键字，然后再次复制该查询。在我看来，这应该始终有效，但事实并非如此。我收到消息“关键字‘联合’附近的语法不正确”。

什么会造成这个问题？

更具体地说，这是完整的查询:

select distinct deliveries.id, orders.id, 20 + sum(orders.mass1) as allowed_duration 
from features_resources  
inner join features on features.id = featureid 
inner join orders on orders.id = features_resources.resourceid 
inner join orderinformations on orders.id = orderinformations.orderid
inner join deliveries on orderinformations.deliveryid = deliveries.id
where features.name = 'O_FRAIS'
and     (deliveries.ID IN
                      (SELECT     ID
                        FROM          dbo.DeliveriesInExportedSchedule))

group by deliveries.id, features.name ,orders.id order by deliveries.id
union
select distinct deliveries.id, orders.id, 20 + sum(orders.mass1) as allowed_duration 
from features_resources  
inner join features on features.id = featureid 
inner join orders on orders.id = features_resources.resourceid 
inner join orderinformations on orders.id = orderinformations.orderid
inner join deliveries on orderinformations.deliveryid = deliveries.id
where features.name = 'O_FRAIS'
and     (deliveries.ID IN
                      (SELECT     ID
                        FROM          dbo.DeliveriesInExportedSchedule))

group by deliveries.id, features.name ,orders.id order by deliveries.id 

我试图通过从一个简单的查询开始并逐个添加功能(内连接、嵌套查询、分组依据、求和...)来在较小的查询上重现错误，但未能重现错误再次。

有什么想法吗？

Answer 1

实际上是上半部分的 order by deliveries.id 导致了问题。

order by 需要应用于整个查询。

示例语法

SELECT v1.number
FROM   master.dbo.spt_values v1
WHERE  v1.number > 2000

UNION

SELECT   v2.number
FROM     master.dbo.spt_values v2
WHERE    v2.number < 10
ORDER BY v1.number