问题描述:

I have the following table:

table: people

id | name | income

==========================

1 Bob 10

2 John 5

3 Amy 15

4 Alyson 5

5 Henry 20

I want to take the average of only a select number of rows, like this:

SELECT

id,

name,

(AVG(

SELECT income FROM people WHERE FIND_IN_SET(id, '1,2,3')

) - income) AS averageDiff

FROM people;

I expect to get a result like this:

id | name | averageDiff

==========================

1 Bob 0

2 John 5

3 Amy -5

4 Alyson -5

5 Henry 10

However, I get an error (#1064) when trying to use the SELECT clause inside of the AVG function. How can I do this?

网友答案:

Use this syntax:

SELECT avg(income) FROM people WHERE FIND_IN_SET(id, '1,2,3')

You need to enclose the above query in brackeds in this way:

SELECT
......
    (ABS(IFNULL(`age`, 0)  
      - IFNULL((SELECT AVG(age) FROM people WHERE FIND_IN_SET(id, '1,2,3')), 0)))
    + (ABS(IFNULL(`income`, 0) 
       - IFNULL((SELECT AVG(income) FROM people WHERE FIND_IN_SET(id, '1,2,3')), 0))) AS sumAvg
FROM `people`
....
网友答案:

If you want the average for everybody as your starting point, calculate that with a query and cross join it to the people you want to include:

SELECT
  people.id,
  people.name,
  people.income - av.avgincome AS averageDiff
FROM people
CROSS JOIN (SELECT AVG(income) AS avgincome FROM people) av
WHERE people.ID IN (1, 2, 3)

If you want the average for the subset of people with ID 1, 2 or 3 as your starting point you can do it like this:

SELECT
  people.id,
  people.name,
  people.income - av.avgincome AS averageDiff
FROM people
CROSS JOIN (
  SELECT AVG(income) AS avgincome
  FROM people
  WHERE ID IN (1, 2, 3)) av
WHERE people.ID IN (1, 2, 3)

Both approaches avoid the correlated subquery (meaning a SELECT for a column name based on the top-level table), which is slow with large recordsets.

The FIND_IN_SET(people.id, '1,2,3') will work, but if you have an index ontable.idtheIN (1, 2, 3)` will be much faster. It will probably be faster even if you don't have the index.

相关阅读:
Top