在SQLAlchemy中,在使用UNION操作后将表连接到自身。

huangapple go评论54阅读模式
英文:

Join table into itself after a union in sqlalchemy

问题

I have two tables:

表一: org,有3列(org_code, chart_code, org_name)
表二: org_2,有2列(org_code, chart_code)

它们代表不同的数据源。

我需要对这两个表进行联合操作,同时从第一张表中获取org_name。

由于这两个表没有完全匹配的列,我认为应该首先在表一和表二上分别进行查询,基于共享的列进行联合查询,然后尝试将查询与第一张表连接起来。

query1 = db.query(org.chart_code, org.org_code).filter(一些筛选条件)

query2 = db.query(org2.chart_code, org2.org_code).filter(一些筛选条件)

union_query = query.union(query2)

联合查询部分运行良好,但在尝试连接时,我无法确定如何指定将org.org_code连接到查询的连接条件。

join_query = union_query.join()

英文:

I have two tables

Table one: org with 3 columns (org_code,chart_code, org_name)
Table two: org_2 with 2 columns (org_code,chart_code)

The represent different sources of data.

I need to do a union on the two but also to get the org_name from the first table.

Since the two don't line-up I figured I should do a union of query on table one and query on table two based on the shared columns and then try to join the query onto the first table

query1 = db.query(org.chart_coded ,org.org_code).filter(some filters)

query2 = db.query(org2.chart_code ,org2.org_code).filter(some filters)
union_query = query.union(query2)

this part of the union works well, but when I am trying to do the join I can't figure out how to specify the join condition of joining org.org_code onto the query.

join_query = union_query.join()

答案1

得分: 1

我不了解SQLAlchemy的语法,但是纯SQL如下:

SELECT org.org_code, org.chart_code, org.org_name
FROM org

UNION

SELECT org2.org_code, org2.chart_code, org1.org_name
FROM org
JOIN org2 ON org.org_code = org2.org_code

英文:

I don't know the SQLAlchemy syntax, but the plain SQL is:

SELECT org.org_code, org.chart_code, org.org_name
FROM org

UNION

SELECT org2.org_code, org2.chart_code, org1.org_name
FROM org
JOIN org2 ON org.org_code = org2.org_code

This

huangapple
  • 本文由 发表于 2023年6月30日 04:38:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/76584468.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定