SQL 在 Firebird 中结合 UNION ALL 和 ORDER BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/354224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combining UNION ALL and ORDER BY in Firebird
提问by Chris
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 unorderedset 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?
括号来自其他数据库的有效语法，需要确保 UNION ALL（定义为在表上工作的操作 - 即无序记录集）的参数不会尝试单独排序。但是我无法让这个语法在 Firebird 中工作 - 怎么做？
回答by Cade Roux
SELECT C1, C2, C3
FROM (
select C1, C2, C3 from T1
union all
select C1, C2, C3 from T2
)
order by C3
回答by Douglas Tosi
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:
字段名称不需要相等。这就是您不能在 order by 中使用字段名称的原因。
您可以改用字段索引。如：
(select C1, C2, C3 from T1)
union all
(select C7, C8, C9 from T2)
order by 3
回答by KCM
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 版本中，如果您按“编号”而不是使用别名订购，它会起作用。
回答by Tiago Moraes
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
回答by Chris
Perform the UNION ALL in a view (without the ORDER BY clause), then select from the view using ORDER BY.
在视图中执行 UNION ALL（不带 ORDER BY 子句），然后使用 ORDER BY 从视图中进行选择。
回答by kai3341
Moving order byinto a query tail has noeffect to output datagrid.
移动order by到查询尾部对输出数据网格没有影响。
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
)