如何获取嵌套子查询以识别父查询列
我有一个问题，我试图计算每个给定 id 的值的总和.我决定使用子查询来做到这一点(通常我会使用连接，但我也为每个子查询保留一个计数器以用于剪辑 - 查看这个问题了解更多信息).为了这个问题，假设我有以下 MySQL 查询:
I have a problem where I'm trying to calculate a sum of values per given id. I decided to do this using sub-queries (typically I'd use a join, but I'm also keeping a counter for each sub-query for clipping purposes - see this question for more info). For the sake of this question, assume I have the following MySQL query:
/* 1. */ SELECT
/* 2. */ t1.experiement_id,
/* 3. */ (SELECT sum(x.size)
/* 4. */ FROM (SELECT size, ( @rownum := @rownum + 1 ) AS `rownum`
/* 5. */ FROM data AS t0
/* 6. */ JOIN ( select @rownum := 0 )
/* 7. */ WHERE t0.experiment_id = t1.experiment_id
/* 8. */ ORDER BY size) AS x
/* 9. */ WHERE x.rownum <= t2.clip_index ) AS `sum`
/* 10. */
/* 11. */ FROM data AS t1
/* 12. */ JOIN data_clip AS t2 USING (experiment_id)
/* 13. */
/* 14. */ GROUP BY t1.experiment_id
问题发生在行 7 上，我试图隔离子查询中与 exeriement_id 匹配的行 - 我收到一个错误 t1.exeriement_id 是未知列.这只发生在嵌套超过 1 级的查询上.就像检查一样，我注意到 t2.clip_index 处理得很好.如果我注释掉第 7 行，则查询返回正常(尽管结果错误).知道如何让子查询识别父表的列以在我的条件下使用吗?谢谢.
The problem happens on row 7, where I'm trying to isolate the rows in the sub-query that matches experiement_id - I'm getting an error that t1.experiement_id is an unknown column. This only happens on a query that's nested more than 1 level deep. Just as a check, I've noticed that t2.clip_index is being processed fine. If I comment out row #7, the query returns fine (albeit with wrong results). Any idea how to make the sub-query recognize the parent table's column to use in my condition? Thanks.
推荐答案
你有没有试过这样的方法来计算行数而不是用户定义的变量?
Have you tried something like this for counting rows instead of the user-defined variable ?
(SELECT sum(size) FROM data AS t0
WHERE t0.experiment_id = t1.experiment_id
ORDER BY size HAVING COUNT(*)<=t2.clip_index
) AS `sum`
让我知道这是否可行，这是我们正在研究的一个有趣的问题.
Let me know if this works, it is an interesting issue we are examining here.
相关文章