MySQL多选？

Question

我正在员工数据库上运行查询。我想返回员工工资超过$x金额的行

SELECT 
    employees_table.employee_id, 
    employees_table.employee_name,
    (SELECT SUM(wages_table.wages)
     FROM wages_table
     WHERE wages_table.employee_id = employees_table.employee_id) AS wages
WHERE wages >= 100"

它返回“未知的列工资”

Answer 1

我认为你想要：

SELECT employees_table.employee_id,
  employees_table.employee_name,
  SUM(wages_table.wages) AS wages
FROM employees_tablee
INNER JOIN wages_table ON wages_table.employee_id = employees_table.employee_id
GROUP BY 1, 2
HAVING SUM(wages_table.wages) >= 100

Answer 2

不能在WHERE子句中使用别名wages >= 100。

我相信您只是想进行一个JOIN和GROUP BY查询。然后，您可以使用允许使用别名的HAVING来过滤工资：

SELECT 
    e.employee_id, 
    e.employee_name,
    SUM(w.wages) AS wages
FROM wages_table w
JOIN employee_table e ON w.employee_id = e.employee_id
GROUP BY e.employee_id
HAVING wages >= 100

Answer 3

尝尝这个

select * from (
SELECT employees_table.employee_id, employees_table.employee_name,
        (SELECT SUM(wages_table.wages)
           FROM wages_table
       WHERE wages_table.employee_id = employees_table.employee_id) AS wages
       )tmp where wages>=100