如何在PostgreSQL中查询时同时获取子查询对应的transactions.id？

一、SQL查询语句 #

你需要通过表连接来同时获取两张表的字段，替代原来的子查询写法，这样就能拿到对应的transactions.id。推荐使用INNER JOIN，因为你只需要匹配到交易记录的字段数据：

SELECT 
    f.id AS field_id,
    f.user_id,
    f.coordinates,
    t.id AS transaction_id
FROM fields f
INNER JOIN transactions t ON f.id = t.field_id
WHERE t.id IN (11, 10, 12);

• 用别名f和t区分两张表的id字段，避免歧义
• 通过t.field_id = f.id建立关联，确保交易记录对应到正确的字段记录
• 筛选条件直接针对transactions.id，和你原需求的ID列表匹配

二、Python操作PostgreSQL 14的示例代码 #

使用psycopg2（PostgreSQL官方推荐的Python驱动）来执行上述查询，示例如下：

1. 先安装依赖：

pip install psycopg2-binary

2. 代码实现：

import psycopg2
from psycopg2.extras import DictCursor

# 替换为你的数据库连接信息
DB_CONFIG = {
    "host": "localhost",
    "database": "your_database",
    "user": "your_username",
    "password": "your_password",
    "port": 5432
}

# 目标交易ID列表
target_transaction_ids = (11, 10, 12)

try:
    # 建立数据库连接（上下文管理器自动处理关闭）
    with psycopg2.connect(**DB_CONFIG) as conn:
        # 使用DictCursor让结果以字典形式返回，方便按字段名取值
        with conn.cursor(cursor_factory=DictCursor) as cur:
            # 执行查询，用ANY(%s)安全传递ID列表，避免SQL注入
            query = """
                SELECT 
                    f.id AS field_id,
                    f.user_id,
                    f.coordinates,
                    t.id AS transaction_id
                FROM fields f
                INNER JOIN transactions t ON f.id = t.field_id
                WHERE t.id = ANY(%s);
            """
            cur.execute(query, (target_transaction_ids,))
            
            # 获取所有查询结果
            records = cur.fetchall()
            
            # 遍历处理结果
            for record in records:
                print(f"字段ID: {record['field_id']}")
                print(f"用户ID: {record['user_id']}")
                print(f"坐标信息: {record['coordinates']}")
                print(f"对应交易ID: {record['transaction_id']}")
                print("-" * 30)

except psycopg2.DatabaseError as e:
    print(f"数据库操作出错: {str(e)}")

说明 #

原来的子查询写法只能筛选出符合条件的fields记录，但无法关联到对应的transactions.id——因为子查询仅作为筛选条件，没有保留交易表的字段信息。通过表连接可以同时关联两张表，一次性获取你需要的所有字段。

内容的提问来源于stack exchange，提问作者Learning from masters