mysql - SQL查询显示不正确的数据

嗨，我有以下数据库scheema

employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager-name)

我需要做以下工作，但据我的培训师说，他们错了。
假设这些公司可能位于几个城市。找到所有公司
位于小银行公司所在的每个城市。

SELECT company-name, city
FROM company
UNION ALL
SELECT company-name, city
FROM company
WHERE company-name = ‘Small Bank Corporation’

找出所有收入高于所有员工平均工资的员工
他们公司的。

SELECT employee-name
FROM works
WHERE salary > ( SELECT AVG(w1.salary)
FROM works w1
INNER JOIN works w2
ON w1.company-name = w2.company-name)

找到员工最多的公司。

SELECT company-name
FROM works
GROUP BY company-name
HAVING count(*) = ( SELECT count(*)
FROM works
GROUP BY company-name
ORDER BY count(*) DESC
LIMIT 1)

找到工资最低的公司。

SELECT company-name
FROM works
GROUP BY company-name
HAVING count(*) < ( SELECT count(*)
FROM works
GROUP BY company-name
ORDER BY count(*) DESC
LIMIT 1)

最佳答案

假设这些公司可能位于几个城市。查找小银行公司所在城市的所有公司。

    SELECT company-name,city FROM company
    WHERE city IN(SELECT city  FROM company
WHERE company-name='Small Bank Corporation');

关于mysql - SQL查询显示不正确的数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15544775/