Mysql嵌套子查询未知列错误 [英] Mysql Nested sub queries unknown column error
问题描述
我有一个非常大的查询,有 2 个子查询.我的大查询更新了整个 E 列.该表如下所示:
i have a pretty big query with 2 sub queries. My big query updates an entire column E. The table looks like this:
--------------------------
id | A | B | E |
--------------------------
1 | NULL | NULL |NULL|
--------------------------
2 | 4 | 6 |NULL|
--------------------------
3 | 6 | 9 |NULL|
--------------------------
这是我在下面的整个查询:
This is my entire query below:
Update mydatabase.mytable ,
(SELECT t1.A/AVG(t2.A)
FROM mytable t1
JOIN mytable t2
ON t2.id <= t1.id
group by t1.id ) AS C,
(SELECT t1.B/AVG(t2.B)
FROM mytable t1
JOIN mytable t2
ON t2.id <= t1.id
group by t1.id ) AS D
SET E = (C+D)/2;
我收到错误:字段列表"中的C"列未知
相信我也会为 D 得到同样的错误.
believe i will also get the same error for D.
我的子查询有效并且语法正确.我只是不确定大查询和更新部分.
My subqueries work and the syntax is correct. i am just unsure of the big query and the update part.
谢谢,
我如何编辑上面查询的 ON 子句部分,以便我希望当前代码在 id<=14(即 t2.id <= t1.id 如上所示)处工作所以当 t1 id =14 时,t2 是从 1 到 14 的所有累积 id,就像现在一样.
How can i edit the ON clause part of the query above such that i would like the current code to work where id<=14 (which is t2.id <= t1.id as shown above) so when t1 id =14, t2 is all the cumulative id from 1 to 14 as it is now.
but 对于 id >14 我希望 ON 子句为 (t2.id=t1.id>=t1.id-2 and <=t1.id)
所以当 t1 id=15 时,t2.id 应该在 13 到 15 之间.当 t1 id =16 时,t2.id 应该在 14 到 16 之间,依此类推.
but for id >14 I would like the ON clause to be (t2.id=t1.id>=t1.id-2 and <=t1.id)
so when t1 id=15, t2.id should be between 13 and 15.
when t1 id =16, t2.id should be between 14 and 16 and so on.
这是因为当我在 id=14 之后计算 ids 的 col E 时,我只对在移动平均值上获取 C 和 D 的前 2 行的平均值感兴趣.
This is because when i calculate col E for ids after id=14, i am only interested in getting the average of the previous 2 rows for C and D on a moving average.
推荐答案
你可以使用单连接重写你的查询,无需额外连接条件来计算另一个值
You can rewrite your query using single join only no need to do additional join with conditions to calculate another value
Update t join
(SELECT t1.A/AVG(t2.A) C ,t1.B/AVG(t2.B) D
FROM t t1
JOIN t t2
ON t2.id <= t1.id
group by t1.id ) AS tt
SET E = (tt.C + tt.D)/2;
针对空值进行编辑
Update t join
(SELECT t1.id ,ifnull(t1.A/AVG(t2.A),0) C ,ifnull(t1.B/AVG(t2.B),0) D
FROM t t1
JOIN t t2
ON t2.id <= t1.id
group by t1.id ) AS tt
on(t.id = tt.id)
SET E = (tt.C + tt.D)/2;
这篇关于Mysql嵌套子查询未知列错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!