如何查看每周持续时间超过20小时的项目的员工ID

Question

(工作中分配的名称为员工分配到项目的时间百分比(

问题：查找分配给每周工作时间超过20小时的项目的员工的ID。

我的尝试：

我假定24小时是这里的工作时间。我的查询正确吗？下面是create和insert语句。

CREATE TABLE IF NOT EXISTS employees(
employee_id NUMERIC(9),
first_name VARCHAR(10),
last_name VARCHAR(20),
dept_code CHAR(5),
salary NUMERIC(9, 2),
PRIMARY KEY (employee_id)
);
CREATE TABLE IF NOT EXISTS departments(
code CHAR(5),
name VARCHAR(30),
manager_id NUMERIC(9),
sub_dept_of CHAR(5),
PRIMARY KEY (code)
);
CREATE TABLE IF NOT EXISTS projects(
project_id CHAR(8),
dept_code CHAR(5),
description VARCHAR(200),
start_date DATE,
stop_date DATE,
revenue NUMERIC(12, 2),
PRIMARY KEY (project_id)
);
CREATE TABLE IF NOT EXISTS works_on(
employee_id NUMERIC(9),
project_id CHAR(8),
assigned_time NUMERIC(3, 2),
PRIMARY KEY (employee_id, project_id)
);
INSERT INTO employees
VALUES (1, 'Al', 'Betheleader', 'ADMIN', 70000),
(2, 'Pl', 'Rsquared', 'ACCNT', 40000),
(3, 'Harry', 'Hardware', 'HDWRE', 50000),
(4, 'Sussie', 'Software', 'CNSLT', 60000),
(5, 'Abe', 'Advice', 'CNSLT', 30000),
(6, 'Hardly', 'Aware', NULL, 65000),
(7, 'Bucky', 'Nour', 'ACTNG', 25000);

INSERT INTO departments
VALUES ('ADMIN', 'Administration', 1, NULL),
('ACCNT', 'Accounting', 2, 'ADMIN'),
('HDWRE', 'Hardware', 3, 'CNSLT'),
('CNSLT', 'Consulting', 4, 'ADMIN'),
('ACTNG', 'Bug fixing', 7, 'ADMIN');
INSERT INTO projects
VALUES 
('EMPHAPPY', 'ADMIN', 'Employee Moral', '2002-03-14', NULL, 0),
('ADT4MFIA', 'ACCNT', 'Mofia Audit', '2003-07-03', '2003-11-30', 100000),
('ROBOSPSE', 'CNSLT', 'Robotic Spouse', '2002-03-14', NULL, 242000),
('DNLDCLNT', 'CNSLT', 'Download Client', '2005-02-03', NULL, 18150),
('BOBBYFUN', 'ACTNG', 'Bug fixing', '2024-02-14', NULL, 17990);
INSERT INTO works_on
VALUES (2, 'ADT4MFIA', 0.50),
(3, 'ROBOSPSE', 0.75),
(4, 'ROBOSPSE', 0.75),
(5, 'ROBOSPSE', 0.50),
(5, 'ADT4MFIA', 0.60),
(3, 'DNLDCLNT', 0.25);

SELECT employee_id
FROM works_on
WHERE 1.68 * assigned_time > 20;

Answer 1

当你提到你在找人时>每周20小时，你不给我们关于"；assigned_time"；是指。这是每天、每周、每月的百分比吗？根据您现在的值，如果您将数学添加到select语句中，您可以看到这些个体中没有一个接近值"20"。

SELECT employee_id, (assigned_time * 1.68) as'worked'
FROM works_on

employee_id worked
2    0.8400
3    0.4200
3    1.2600
4    1.2600
5    1.0080
5    0.8400

我想你正在寻找同一员工身份证多次工作的总和，这将更符合

SELECT employee_id, sum(assigned_time * 1.68) as'worked'
FROM works_on
group by employee_id

employee_id worked
2    0.8400
3    1.6800
4    1.2600
5    1.8480