php - 选择第二天轮类休息的用户，我想在后端检查第一天哪个不应该休息

Question

$first_day = $_POST['day1'];
$second_day = $_POST['day2'];
$sql = "SELECT * FROM users_shift WHERE userid!='$_SESSION[userid]' AND grp='$user_group' AND date_start = '$second_day' AND shift='Dayoff' ORDER BY name ASC";

我希望那些第一天没有休息日的用户应该出现。但我想展示的结果是第二天的结果。只是想在后端检查用户第一天不应该休息。

Answer 1

您可以使用子查询来选择用户的ID。子查询是:

select userid from users_shift where userid!='$_SESSION[userid]' AND grp='$user_group' and date_start = '$first_day' AND shift!='Dayoff' 

完整查询是:

$first_day = $_POST['day1'];
$second_day = $_POST['day2'];
$sql = "SELECT * FROM users_shift WHERE userid!='$_SESSION[userid]' AND grp='$user_group' AND date_start = '$second_day' AND shift='Dayoff' and userid in(select userid from users_shift where userid!='$_SESSION[userid]' AND grp='$user_group' and date_start = '$first_day' AND shift!='Dayoff') ORDER BY name ASC";

或者您可以使用像这样的内部联接:

select * from users_shift s 
inner join users_shift f 
on s.userid = f.userid where 
where s.userid!='$_SESSION[userid]' AND s.grp='$user_group' And
s.date_start = '$first_day' AND s.shift!='Dayoff' And f.date_start = 
'$second_day' And f.shift = 'Dayoff';

附注查询未测试。