使用peewee实现多聚合查询：获取每个A对应x、y最小值关联的z值

问题原因 #

你之前的写法无法得到正确结果的核心原因是：同一个A记录对应的最小x值、最小y值大概率属于B表的不同行，直接在单次JOIN的分组查询中取B.z，数据库只会返回分组内任意一行的z值，所以会出现z_x和z_y值重复覆盖的问题。

原生SQL实现方案 #

提供两种通用实现，可根据你使用的数据库版本选择：

方案1：窗口函数实现（推荐，支持MySQL8.0+、PostgreSQL、SQLite 3.25+等新版本数据库）

SELECT 
    t2.id AS A_id,
    MIN(CASE WHEN rn_x = 1 THEN t1.x END) AS x,
    MIN(CASE WHEN rn_x = 1 THEN t1.z END) AS z_x,
    MIN(CASE WHEN rn_y = 1 THEN t1.y END) AS y,
    MIN(CASE WHEN rn_y = 1 THEN t1.z END) AS z_y
FROM A AS t2
INNER JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY A_key ORDER BY x ASC) AS rn_x,
        ROW_NUMBER() OVER (PARTITION BY A_key ORDER BY y ASC) AS rn_y
    FROM B
) AS t1 ON t1.A_key = t2.id
GROUP BY t2.id

实现逻辑：先对B表按关联的A_id分组，分别给每个分组内的记录按x、y升序排名，排名为1的就是对应列最小值所在的行，最后按A分组取出对应的值即可。

方案2：子查询关联实现（兼容旧版本不支持窗口函数的数据库）

SELECT
    t2.id AS A_id,
    tx.min_x AS x,
    tx.z AS z_x,
    ty.min_y AS y,
    ty.z AS z_y
FROM A AS t2
-- 关联获取x最小值对应的z
INNER JOIN (
    SELECT b1.A_key, b1.x AS min_x, b1.z 
    FROM B b1
    WHERE b1.x = (SELECT MIN(b2.x) FROM B b2 WHERE b2.A_key = b1.A_key)
) AS tx ON tx.A_key = t2.id
-- 关联获取y最小值对应的z
INNER JOIN (
    SELECT b1.A_key, b1.y AS min_y, b1.z 
    FROM B b1
    WHERE b1.y = (SELECT MIN(b2.y) FROM B b2 WHERE b2.A_key = b1.A_key)
) AS ty ON ty.A_key = t2.id

对应Peewee实现（以窗口函数方案为例） #

from peewee import fn, Case

# 构造带排名的B表CTE子查询
B_ranked = B.select(
    B,
    fn.ROW_NUMBER().over(partition_by=[B.A_key], order_by=[B.x.asc()]).alias('rn_x'),
    fn.ROW_NUMBER().over(partition_by=[B.A_key], order_by=[B.y.asc()]).alias('rn_y')
).cte('B_ranked')

# 主查询
query = (A
         .select(
             A.id.alias('A_id'),
             fn.MIN(Case(None, [(B_ranked.c.rn_x == 1, B_ranked.c.x)])).alias('x'),
             fn.MIN(Case(None, [(B_ranked.c.rn_x == 1, B_ranked.c.z)])).alias('z_x'),
             fn.MIN(Case(None, [(B_ranked.c.rn_y == 1, B_ranked.c.y)])).alias('y'),
             fn.MIN(Case(None, [(B_ranked.c.rn_y == 1, B_ranked.c.z)])).alias('z_y')
         )
         .join(B_ranked, on=(B_ranked.c.A_key == A.id))
         .group_by(A.id)
         .with_cte(B_ranked))

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