SQL计算与join同时工作的人员

Question

我是SQL新手。

我想统计在特定时间工作的员工。

数据模式有一个Person表和一个Shifts表。它们由一个StaffShifts表连接，该表同时具有user_id字段和shift_id字段。

每个员工可以有多个班次，每个班次可以有多个员工。

create table Person
(
user_id INT,
rank_id INT,
groupschedule_id INT,
personnum VARCHAR(6),
PRIMARY KEY (user_id)
);
INSERT INTO Person (user_id, rank_id, groupschedule_id, personnum)
VALUES
(1, 1, 1, 'ABC123'),
(2, 1, 2, 'DEF456'),
(3, 2, 3, 'GHI789'),
(4, 1, 1, 'JKL123'),
(5, 3, 2, 'NOP123'),
(6, 1, 3, 'RST789'),
(7, 2, 1, 'WXY789'),
(8, 1, 2, 'ABC432'),
(9, 1, 3, 'DEF789')
;
CREATE TABLE Groupschedule
(
groupschedule_id INT,
shortnm char(20)
);
INSERT INTO Groupschedule
VALUES
(1,'TEAM 1'),
(2,'TEAM 2'),
(3,'TEAM 3')
;
CREATE TABLE Shifts
(
shift_id INT PRIMARY KEY,
shift_start datetime,
shift_end datetime
);
INSERT INTO Shifts
VALUES
(1, '2021-03-08 06:45:00', '2021-03-08 15:00:00'),
(2, '2021-03-08 14:00:00', '2021-03-08 23:00:00'),
(3, '2021-03-08 23:00:00', '2021-04-09 07:00:00')
;

CREATE TABLE Osl
(
shift_id INT,
osl INT,
area char(10),
FOREIGN KEY (shift_id) REFERENCES Shifts(shift_id)
);
INSERT INTO Osl
VALUES
(1,3, 'EAST'),
(2,2, 'EAST'),
(3,2, 'EAST')
;
CREATE TABLE StaffShifts
( shift_id INT,
user_id INT,
FOREIGN KEY (shift_id) REFERENCES Shifts(shift_id),
FOREIGN KEY (user_id) REFERENCES Person(user_id)
);

what BEEN TRIED

我试着从检索当时工作的所有人员开始:

SELECT shift_id FROM Shifts WHERE shift_start < '2021-03-08 11:00:00' AND shift_end > '2021-03-08 11:00:00' 
INNER JOIN StaffShifts ON Person.user_id=StaffShifts.user_id

在小提琴上，这将导致引用INNER JOIN但不详细说明的错误。

我在这里创建了一个提琴https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1572ed28005766d30a18521557aadb90

I have try SQL statement:

SELECT shift_id FROM Shifts INNER JOIN StaffShifts ON Person.user_id=StaffShifts.user_id 
WHERE shift_start < '2021-03-08 11:00:00' AND shift_end > '2021-03-08 11:00:00' 

但是这会产生错误，shift_id是不明确的。

因为我真的想要COUNT的用户工作一个给定的班次，我试图返回一个用户列表-使用连接两个一对多的关系:

SELECT Person.user_id
FROM Person 
INNER JOIN StaffShifts ON Person.user_id=StaffShifts.user_id
INNER JOIN StaffShifts ON Shifts.shift_id=StaffShifts.shift_id
WHERE Shifts.shift_start < '2021-03-08 11:00:00' 
AND Shifts.shift_end > '2021-03-08 11:00:00'  

但是这会导致'Not unique table/alias: 'StaffShifts "

注意，在返回person列表之前，我没有尝试使用COUNT。

Answer 1

您需要包括Person-您想要计数，StaffShifts-其中一个人被分配到一个班次，因此您也可以基于此加入Shifts，以及Shifts-您检查您希望的小时范围。

SELECT
COUNT(*)
FROM `Person`
INNER JOIN `StaffShifts` ON `Person`.`user_id` = `StaffShifts`.`user_id`
INNER JOIN `Shifts` ON `StaffShifts`.`shift_id` = `Shifts`.`shift_id`
WHERE 
Shifts.shift_start < '2021-03-08 11:00:00' 
AND Shifts.shift_end > '2021-03-08 14:00:00'
;