按 SqlAlchemy SQLite 中联合查询中的列排序 [英] Sort by a column in a union query in SqlAlchemy SQLite

问题描述

如这个问题中所述，你可以在unions中使用字符串文字来做order by.

As explained in this question, you can use string literals to do order by in unions.

例如，这适用于 Oracle:

For example, this works with Oracle:

querypart1 = select([t1.c.col1.label("a")]).order_by(t1.c.col1).limit(limit)
querypart2 = select([t2.c.col2.label("a")]).order_by(t2.c.col2).limit(limit)
query = querypart1.union_all(querypart2).order_by("a").limit(limit)

order-by 可以采用字符串字面量，即联合结果中列的名称.

The order-by can take a string literal, which is the name of the column in the union result.

(分区表中有无数行，我正在尝试对这些该死的东西进行分页)

(There are gazillions of rows in partitioned tables and I'm trying to paginate the damn things)

然而，当针对 SQLite3 运行时，这会产生一个异常:

When running against SQLite3, however, this generates an exception:

sqlalchemy.exc.OperationalError: (OperationalError) near "ORDER": syntax error

你如何order byunion的结果?

推荐答案

属于联合查询一部分的查询不得排序.

The queries that are part of a union query must not be sorted.

为了能够在复合查询中使用限制，您必须将各个查询包装在单独的子查询中:

To be able to use limits inside a compound query, you must wrap the individual queries inside a separate subquery:

SELECT * FROM (SELECT ... LIMIT ...)
UNION ALL
SELECT * FROM (SELECT ... LIMIT ...)

q1 = select(...).limit(...).subquery()
q2 = select(...).limit(...).subquery()
query = q1.union_all(q2)...

这篇关于按 SqlAlchemy SQLite 中联合查询中的列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！