如何在SQLAlchemy中实现嵌套查询/CTE？解决用户照片数聚合查询问题

问题1：两种SQL写法的正确性 #

你给出的两种SQL写法都不正确，核心问题在于缺少GROUP BY子句，且查询逻辑不符合一对多关联的统计需求：

• 聚合函数COUNT()如果不配合GROUP BY，会将整个表视为一组，返回的是全表照片总数，而非每个用户的照片数。
• 在标准SQL中，SELECT列表里的非聚合字段（如user.id、user.email）必须出现在GROUP BY中，否则会触发语法错误（部分宽松模式的数据库可能允许，但结果不可控）。
• 直接查询user.photos没有意义——一对多关系下，每个用户对应多条照片记录，未分组的情况下会返回重复的用户数据，且聚合后无法关联到具体的照片条目。

修正后的正确写法

假设表结构为：users表（主键id）和photos表（外键user_id关联users.id），正确的统计逻辑是按用户分组，统计每个用户关联的照片数量：

嵌套查询写法

SELECT u.*
FROM (
    SELECT u.id, COUNT(p.id) AS photos_count
    FROM users u
    LEFT JOIN photos p ON u.id = p.user_id
    GROUP BY u.id
) AS user_photo_counts
JOIN users u ON u.id = user_photo_counts.id
WHERE user_photo_counts.photos_count = 1;

CTE写法

WITH user_photo_counts AS (
    SELECT u.id, COUNT(p.id) AS photos_count
    FROM users u
    LEFT JOIN photos p ON u.id = p.user_id
    GROUP BY u.id
)
SELECT u.*
FROM users u
JOIN user_photo_counts upc ON u.id = upc.id
WHERE upc.photos_count = 1;

问题2：转换为SQLAlchemy代码 #

以下是对应两种写法的SQLAlchemy实现（假设User模型和Photo模型已定义，且User.photos是一对多的关系属性）：

子查询（嵌套SELECT）写法

import sqlalchemy as sa

# 子查询：统计每个用户的照片数
subq = sa.select(
    User.id,
    sa.func.count(Photo.id).label("photos_count")
).join(User.photos, isouter=True)  # 左连保留无照片的用户，若只需要有照片的用户可去掉isouter=True
.group_by(User.id)
.subquery()

# 主查询：筛选照片数为1的用户
query = sa.select(User).join(
    subq, User.id == subq.c.id
).where(subq.c.photos_count == 1)

CTE写法

import sqlalchemy as sa

# 定义CTE：统计每个用户的照片数
cte = sa.select(
    User.id,
    sa.func.count(Photo.id).label("photos_count")
).join(User.photos, isouter=True)
.group_by(User.id)
.cte("user_photo_counts")

# 主查询：关联CTE并筛选
query = sa.select(User).join(
    cte, User.id == cte.c.id
).where(cte.c.photos_count == 1)

另外，也可以用EXISTS子查询实现，性能可能更优：

query = sa.select(User).where(
    sa.exists(
        sa.select(sa.func.count(Photo.id))
        .where(Photo.user_id == User.id)
        .having(sa.func.count(Photo.id) == 1)
    )
)

内容的提问来源于stack exchange，提问作者Olivier Berg