结合所有和秩序在火鸟

This is my first attempt at answering my own question, since someone may well run into this and so it might be of help. Using Firebird, I want to combine the results of two queries using UNION ALL, then sort the resulting output on a given column. Something like:

这是我第一次回答我自己的问题，因为有人可能会遇到这个问题，所以这可能会有帮助。使用Firebird，我希望使用UNION ALL合并两个查询的结果，然后对给定列上的结果进行排序。喜欢的东西:

(select C1, C2, C3 from T1)
union all 
(select C1, C2, C3 from T2)
order by C3

The parentheses came from valid syntax for other databases, and are needed to make sure the arguments to UNION ALL (an operation that's defined to work on tables - i.e. an unordered set of records) don't try to be ordered individually. However I couldn't get this syntax to work in Firebird - how can it be done?

圆括号来自其他数据库的有效语法，需要确保联合ALL的参数(定义为在表上工作的操作——即无序的记录集)不尝试单独排序。但是，我不能让这个语法在Firebird中工作——怎么做呢?

6 个解决方案

#1

SELECT C1, C2, C3
FROM (
    select C1, C2, C3 from T1
    union all 
    select C1, C2, C3 from T2
)
order by C3

#2

Field names are not required to be equal. That's why you can't use the field name in the order by.
You may use the field index instead. As in:

字段名不要求相等。这就是为什么不能按顺序使用字段名的原因。您可以使用字段索引。如:

(select C1, C2, C3 from T1)
union all 
(select C7, C8, C9 from T2)
order by 3

#3

How about:

如何:

select C1, C2, C3 from T1
union all 
select C1, C2, C3 from T2
order by 2

At least in the newer Firebird Versions it works if you order by "Number" instead of using an Alias.

至少在更新的Firebird版本中，如果按“Number”排序，而不是使用别名，那么它就可以工作。

#4

In Firebird 1.5 this works for me

在Firebird 1.5中，这对我很有效

create view V1 (C1, C2, C3) as
  select C1, C2, C3 from T1
  union all 
  select C1, C2, C3 from T2

and then

然后

select C1, C2, C3 from V1 order by C3

#5

Perform the UNION ALL in a view (without the ORDER BY clause), then select from the view using ORDER BY.

在一个视图中执行联合(没有ORDER BY子句)，然后使用ORDER BY从视图中选择。

#6

Moving order by into a query tail has no effect to output datagrid.

将顺序移动到查询尾部对输出datagrid没有影响。

select * from (
    select first 1
        C1
    from T1
    order by id desc
)
union all
select * from (
    select first 1
        C1
    from T2
    order by id desc
)

#1

SELECT C1, C2, C3
FROM (
    select C1, C2, C3 from T1
    union all 
    select C1, C2, C3 from T2
)
order by C3

#2