mysql - mysql子查询中的未知列

Question

我正在尝试获取项目的平均值，因此我使用了子查询。

更新:我一开始应该更清楚，但我希望平均只适用于最后 5 项

首先我开始

SELECT 
y.id
FROM (
    SELECT *
        FROM (
                SELECT *
                FROM products
                WHERE itemid=1
        ) x  
    ORDER BY id DESC
    LIMIT 15 
) y;

它运行但相当无用，因为它只是向我显示 ID。

然后我在下面添加了

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;

当我这样做时，我得到错误 Unknown column 'y.id' in 'where clause'，在这里进一步阅读后，我相信这是因为当查询进入下一个级别时，它们需要加入吗？

所以我尝试了下面的**删除了不需要的 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'不存在。我在正确的轨道上吗？我需要改变什么才能得到我想要的东西？

示例可以在 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 次存款的平均值。

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

Answer 1

我不是 MySQL 专家(在 MS SQL 中可以更轻松地完成)，您的问题对我来说似乎有点不清楚，但看起来您正在尝试获取前 5 项的平均值。

如果你有Id without gaps，这很容易:

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

如果不是，那么我已经尝试过这样的查询

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

但我有异常'where 子句'中的未知列'p.id'。看起来 MySQL 无法处理 2 级嵌套的子查询。但是你可以用 offset 得到 5 个之前的项目，像这样:

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

sql fiddle demo