mysql - 在 AVG 函数中使用 SELECT 子句

Question

我有下表:

table: people
id  |   name    |   income
==========================
1    Bob         10
2    John        5
3    Amy         15
4    Alyson      5
5    Henry       20

我只想取选定行数的平均值，如下所示:

SELECT
    id,
    name,
    (AVG(
        SELECT income FROM people WHERE FIND_IN_SET(id, '1,2,3')
    ) - income) AS averageDiff
FROM people;

我希望得到这样的结果:

id  |   name    |   averageDiff
==========================
1    Bob         0
2    John        5
3    Amy         -5
4    Alyson      -5
5    Henry       10

但是，当我尝试在 AVG 函数中使用 SELECT 子句时出现错误 (#1064)。我该怎么做？

Answer 1

使用这个语法:

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

您需要以这种方式将上述查询括在括号中:

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`
....