如何获取嵌套的子查询以识别父查询列 [英] How to get a nested sub-query to recognize parent query column

问题描述

我在尝试计算每个给定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 上发生，我试图在子查询中隔离与 experiement_id -我收到 t1.experiement_i的错误d 是未知列。只有在嵌套了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.

这篇关于如何获取嵌套的子查询以识别父查询列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！