mysql - 在查询中与其他列一起显示平均值

Question

我想显示一个成员列表，该列表超过所有其他成员的平均值。我的表格如下所示:

CREATE TABLE Members
(
    MemberID nvarchar(50) NOT NULL PRIMARY KEY, 
    MemberName nvarchar(50) NOT NULL, 
    MemberAddress nvarchar(50),
    MemberContact int,
    MemberAge int,
    RegistrationDate nvarchar(50) 
);


--Inserting Values into Members' Table--
INSERT INTO Members(MemberID, MemberName, MemberAddress, MemberContact, MemberAge,RegistrationDate)
VALUES ('MEM01', 'Muhammed Abdul', 'Damansara','01121565987', '34', '20/02/2017');
INSERT INTO Members(MemberID, MemberName, MemberAddress, MemberContact, MemberAge,RegistrationDate)
VALUES ('MEM02', 'Kyle Franklin', 'Kuala Lumpur','01725445807','36', '10/04/2017');
INSERT INTO Members(MemberID, MemberName, MemberAddress, MemberContact, MemberAge,RegistrationDate)
VALUES ('MEM03', 'Albert Derio', 'Shah Alam','01800235613','56','6/03/2017');
INSERT INTO Members(MemberID, MemberName, MemberAddress, MemberContact, MemberAge,RegistrationDate)
VALUES ('MEM04', 'Khaled Ahmad', 'Penang','01602144956','33','9/01/2017');

这是我到目前为止所尝试过的，但没有成功:

SELECT MemberID, MemberName, MemberAge,FROM Members 
(SELECT AVG(Members.MemberAge) AS AverageAge)
WHERE MemberAge>=AverageAge

Answer 1

SELECT
    Members.*,
    (SELECT AVG(MemberAge) FROM Members) AS AverageAge
FROM
    Members
WHERE
    MemberAge > (SELECT AVG(MemberAge) FROM Members)

...尽管这会导致新的 AverageAge 列中出现重复数据 - 批处理查询(返回多个结果集的查询)会更干净:

MySQL 有隐式变量声明(与需要前向声明的 T-SQL 不同)，因此您可以这样做:

-- 1. Get the average-age value:
SELECT @averageAge = AVG(MemberAge) FROM Members;

-- 2. Return the average-age value to the database client as a scalar value:
SELECT @averageAge;

-- 3. Query for Members whose age is above-average in a second batch result:
SELECT
    Members.*
FROM
    Members
WHERE
    MemberAge > @averageAge