如何在 SQLAlchemy 和 postgresql 中联合两个子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20024744/
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
How to union two subqueries in SQLAlchemy and postgresql
提问by kkaehler
Raw SQL desired:
所需的原始 SQL：
SELECT
id
FROM
(SELECT some_table.id FROM some_table WHERE some_table.some_field IS NULL) AS subq1
UNION
(SELECT some_table.id WHERE some_table.some_field IS NOT NULL)
LIMIT 10;
Here is the python code:
这是python代码：
import sqlalchemy
SOME_TABLE = sqlalchemy.Table(
'some_table',
sqlalchemy.MetaData(),
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('some_field', sqlalchemy.Text))
stmt_1 = sqlalchemy.sql.select(SOME_TABLE.columns).where(SOME_TABLE.columns.some_field != None)
stmt_2 = sqlalchemy.sql.select(SOME_TABLE.columns).where(SOME_TABLE.columns.some_field == None)
# This gets a programming error.
stmt_1.union(stmt_2).limit(10);
Here is the outputted SQL (with parameters swapped in) that gets this error: ERROR: syntax error at or near "UNION":
以下是输出的 SQL（参数已交换）出现此错误： 错误：“UNION”处或附近的语法错误：
SELECT some_table.id, some_table.some_field
FROM some_table
WHERE some_table.some_field IS NOT NULL
LIMIT 10 UNION SELECT some_table.id, some_table.some_field
FROM some_table
WHERE some_table.some_field IS NULL
LIMIT 10
LIMIT 10
How can I alias the subqueries?
如何为子查询设置别名？
回答by jbub
i used a little bit different approach:
我使用了一些不同的方法：
# the first subquery, select all ids from SOME_TABLE where some_field is not NULL
s1 = select([SOME_TABLE.c.id]).where(SOME_TABLE.c.some_field != None)
# the second subquery, select all ids from SOME_TABLE where some_field is NULL
s2 = select([SOME_TABLE.c.id]).where(SOME_TABLE.c.some_field != None)
# union s1 and s2 subqueries together and alias the result as "alias_name"
q = s1.union(s2).alias('alias_name')
# run the query and limit the aliased result to 10
session.query(q).limit(10)
Here is the produced sql:
这是生成的sql：
SELECT alias_name.id AS alias_name_id
FROM (SELECT some_table.id AS id
FROM some_table
WHERE some_table.some_field IS NOT NULL UNION SELECT some_table.id AS id
FROM some_table
WHERE some_table.some_field IS NULL) AS alias_name
LIMIT 10
I think this is the result you wanted.
我想这就是你想要的结果。
