时间戳

首页 > 解决方案 > 找出哪个员工接受了更多培训

sql - 找出哪个员工接受了更多培训

问题描述

我有 2 张桌子，员工和 classes_taken。我想看看哪些员工上过超过 2 节课。但是，我收到此错误ERROR: aggregate functions are not allowed in WHERE Position: 87。

CREATE TABLE employees (
  id integer primary key,
  name text
);

CREATE TABLE classes_taken (
  employee integer,
  class text,
  foreign key (employee) references employees(id)
);

INSERT INTO employees (id, name) VALUES
(1, 'bob'), (2, 'sam'), (3, 'mike');

INSERT INTO classes_taken (employee, class) VALUES
(1, 'swimming'), (1, 'dancing'), (2, 'swimming'), (2, 'tennis'), (3, 'golf'), (3, 'dancing');

我的选择声明。

select e.id, e.name
FROM employees e 
JOIN classes_taken c
ON e.id = c.employee
WHERE count(c.class) > 2
GROUP BY c.class;

SQLFiddle：http ://sqlfiddle.com/#!15/5296cb/4

标签： sqldatabasepostgresqlselect

解决方案

您需要将计数过滤器保留在 HAVING 子句中，因为它位于聚合值上。

select e.id, e.name
FROM employees e 
INNER JOIN classes_taken c ON e.id = c.employee 
GROUP BY e.id, e.name
HAVING count(c.class) > 2

推荐阅读