SQL查询一对多的关系

Question

这里是我的数据库：

Employee表：
employee_id int(10) NOT NULL,
firstname varchar(50) NOT NULL,
lastname varchar(50) NOT NULL,
username varchar(15) NOT NULL,
password varchar(25) NOT NULL DEFAULT 'password',
contact_number varchar(13) NOT NULL,
email_address varchar(50) NOT NULL,
position varchar(50) NOT NULL,
teamleader_id int(11) DEFAULT NULL

服务表：
service_id int(10) NOT NULL,
ticket_id int(10) NOT NULL,
employee_id int(10) NOT NULL,
status varchar(15) NOT NULL,
start_time datetime NOT NULL,
time_in datetime DEFAULT NULL,
time_out datetime DEFAULT NULL,
service_notes varchar(500) DEFAULT NULL

查询：

SELECT * FROM employee AS e 
LEFT JOIN service AS s ON e.employee_id = s.employee_id 
WHERE (s.status IS NULL OR s.status = 'Completed') 
AND e.teamleader_id = ? 

编辑：

我要选择除与service.status的那些所有员工= '正在进行'

Answer 1

假设你只想员工列表已完成服务的人员（不包括那些没有且仅显示每位员工一项服务的人）

SELECT employee.*, COUNT(service.status) 
FROM employee, service 
WHERE service.employee_id = employee.employee_id 
AND (service.status IS NULL OR service.status = 'Completed') 
AND teamleader_id = 1 
GROUP BY employee.employee_id; 

或者如果你想列出谁没有完成任何在职员工

SELECT employee.*, COUNT(service.status) 
FROM employee LEFT JOIN service ON service.employee_id = employee.employee_id 
WHERE (service.status IS NULL OR service.status = 'Completed') 
AND teamleader_id = 1 
GROUP BY employee.employee_id; 

，或者如果你希望所有除非service.status =“正在进行”

SELECT employee.*, COUNT(service.status) 
FROM employee LEFT JOIN service ON service.employee_id = employee.employee_id 
WHERE employee.employee_id NOT IN (SELECT DISTINCT service.employee_id FROM service WHERE service.status = 'Ongoing') 
AND teamleader_id = 1 
GROUP BY employee.employee_id; 

在SQL Fiddle

测试

CREATE TABLE employee (employee_id INT(9) PRIMARY KEY, teamleader_id INT NOT NULL, name VARCHAR(99) NOT NULL); 
CREATE TABLE service (id INT(9) PRIMARY KEY, employee_id INT(9) NOT NULL, status VARCHAR(99) NOT NULL); 
INSERT INTO employee VALUES (1, 1, 'Bob'); 
INSERT INTO employee VALUES (2, 1, 'Alice'); 
INSERT INTO service VALUES (1, 2, 'Complete'); 
INSERT INTO service VALUES (2, 2, 'WIP'); 
INSERT INTO service VALUES (3, 2, 'Ongoing'); 

Answer 2

你只需要DISTINCT添加到您的查询：

SELECT DISTINCT e.* FROM employee e LEFT JOIN service s ON e.employee_id = s.employee_id 
WHERE(s.status is null OR s.status = 'Completed') and teamleader_id = 3 

它过滤重复