如何在SQLAlchemy和postgresql中联合两个子查询

6xfqseft 于 2022-11-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(148)

所需的原始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;

下面是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);

以下是出现此错误的输出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

如何为子查询设置别名？

postgresql

来源：https://stackoverflow.com/questions/20024744/how-to-union-two-subqueries-in-sqlalchemy-and-postgresql

1条答案

按热度按时间

yhived7q1#

我用了一个稍微不同的方法：

# 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)

下面是生成的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

我想这就是你想要的结果。

赞(0）回复(0）举报 2022-11-29

我来回答

如何在SQLAlchemy和postgresql中联合两个子查询

1条答案

相关问题

热门标签

最新问答