mysql动态查询未知列,mysql子查询中的未知列

I am trying to get the avg of an item so I am using a subquery.

Update: I should have been clearer initially, but i want the avg to be for the last 5 items only

First I started with

SELECT

y.id

FROM (

SELECT *

FROM (

SELECT *

FROM products

WHERE itemid=1

) x

ORDER BY id DESC

LIMIT 15

) y;

Which runs but is fairly useless as it just shows me the ids.

I then added in the below

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;

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?

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;

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?

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);

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

解决方案

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.

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

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