mysql - SQL查询将特定列与同一列的平均值进行比较

我的架构如下

doctor (doctor_name, residence_address, postal_code, year_experience)
works (doctor_name, branch_name, annual_pay)
branch (branch_name, branch_address, postal_code)
patient (patient_name, doctor_name, branch_name)

我被要求检索年薪高于“新加坡”分支机构名称中所有医生的平均年薪的Doctor_name。

如何在sql查询中执行此操作。我正在使用MySQL，到目前为止，我已经尝试过了

SELECT doctor.doctor_name, doctor.residence_address
FROM doctor INNER JOIN works on doctor.doctor_name = works.doctor_name
WHERE avg(annual_pay)< annual pay

最佳答案

请尝试以下查询：

SELECT doctor_name
FROM doctor
INNER JOIN works ON doctor.doctor_name = works.doctor_name
WHERE annual_pay > (
        SELECT avg(annual_pay)
        FROM works
        )
    AND branch_name = 'Singapore'

关于mysql - SQL查询将特定列与同一列的平均值进行比较，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52033389/