MySQL子查询中的未知列 [英] Unknown column in mysql subquery
问题描述
我正在尝试获取商品的平均值，因此我在使用子查询.
I am trying to get the avg of an item so I am using a subquery.
更新:最初我应该更清楚一些，但我希望平均水平仅适用于最后5个项目
Update: I should have been clearer initially, but i want the avg to be for the last 5 items only
首先我开始
SELECT
y.id
FROM (
SELECT *
FROM (
SELECT *
FROM products
WHERE itemid=1
) x
ORDER BY id DESC
LIMIT 15
) y;
运行哪个，但因为它只是向我显示ID而没有用.
Which runs but is fairly useless as it just shows me the ids.
然后我在下面添加了
SELECT
y.id,
(SELECT AVG(deposit) FROM (SELECT deposit FROM products WHERE id < y.id ORDER BY id DESC LIMIT 5)z) AVGDEPOSIT
FROM (
SELECT *
FROM (
SELECT *
FROM products
WHERE itemid=1
) x
ORDER BY id DESC
LIMIT 15
) y;
执行此操作时，出现错误'where子句'中的未知列'y.id'，在此继续阅读时，我相信这是因为当查询下降到下一级时，它们会需要加入吗?
When I do this I get the error Unknown column 'y.id' in 'where clause', upon further reading here I believe this is because when the queries go down to the next level they need to be joined?
所以我尝试了下面的**删除了不需要的suquery
So I tried the below ** removed un needed suquery
SELECT
y.id,
(SELECT AVG(deposit) FROM (
SELECT deposit
FROM products
INNER JOIN y as yy ON products.id = yy.id
WHERE id < yy.id
ORDER BY id DESC
LIMIT 5)z
) AVGDEPOSIT
FROM (
SELECT *
FROM products
WHERE itemid=1
ORDER BY id DESC
LIMIT 15
) y;
但是我得到表'test.y'不存在.我在正确的轨道上吗?我需要改变什么才能在这里得到我想要的东西?
But I get Table 'test.y' doesn't exist. Am I on the right track here? What do I need to change to get what I am after here?
可以在sqlfiddle中的此处找到该示例.
The example can be found here in sqlfiddle.
CREATE TABLE products
(`id` int, `itemid` int, `deposit` int);
INSERT INTO products
(`id`, `itemid`, `deposit`)
VALUES
(1, 1, 50),
(2, 1, 75),
(3, 1, 90),
(4, 1, 80),
(5, 1, 100),
(6, 1, 75),
(7, 1, 75),
(8, 1, 90),
(9, 1, 90),
(10, 1, 100);
鉴于本例中的数据，我的预期结果如下所示，每个ID旁边都有一列，该列具有前5次存款的平均值.
Given my data in this example, my expected result is below, where there is a column next to each ID that has the avg of the previous 5 deposits.
id | AVGDEPOSIT
10 | 86 (deposit value of (id9+id8+id7+id6+id5)/5) to get the AVG
9 | 84
8 | 84
7 | 84
6 | 79
5 | 73.75
推荐答案
我不是MySQL专家(在MS SQL中可以更轻松地完成)，您的问题对我来说似乎有点不清楚，但看起来像您试图获取前5个项目的平均值.
I'm not an MySQL expert (in MS SQL it could be done easier), and your question looks a bit unclear for me, but it looks like you're trying to get average of previous 5 items.
如果您具有没有空格的ID ，那么很简单:
If you have Id without gaps, it's easy:
select
p.id,
(
select avg(t.deposit)
from products as t
where t.itemid = 1 and t.id >= p.id - 5 and t.id < p.id
) as avgdeposit
from products as p
where p.itemid = 1
order by p.id desc
limit 15
如果不是，那么我已经尝试过这样的查询
If not, then I've tri tried to do this query like this
select
p.id,
(
select avg(t.deposit)
from (
select tt.deposit
from products as tt
where tt.itemid = 1 and tt.id < p.id
order by tt.id desc
limit 5
) as t
) as avgdeposit
from products as p
where p.itemid = 1
order by p.id desc
limit 15
但是我有异常Unknown column 'p.id' in 'where clause'.看起来MySQL无法处理2级嵌套的子查询.
但是您可以通过offset获得5个先前的项目，例如:
But I've got exception Unknown column 'p.id' in 'where clause'. Looks like MySQL cannot handle 2 levels of nesting of subqueries.
But you can get 5 previous items with offset, like this:
select
p.id,
(
select avg(t.deposit)
from products as t
where t.itemid = 1 and t.id > coalesce(p.prev_id, -1) and t.id < p.id
) as avgdeposit
from
(
select
p.id,
(
select tt.id
from products as tt
where tt.itemid = 1 and tt.id <= p.id
order by tt.id desc
limit 1 offset 6
) as prev_id
from products as p
where p.itemid = 1
order by p.id desc
limit 15
) as p
这篇关于MySQL子查询中的未知列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
