Mysql，显示谁已经选择退出和没有选择退出巴士服务

Question

我有学生和事件表通过sid链接。

CREATE TABLE `students` (
`sid` int(8) NOT NULL COMMENT 'use',
`active` enum('Yes','No','vac','Grad') NOT NULL DEFAULT 'Yes',
`name` varchar(130) DEFAULT NULL,
`bus` varchar(130) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `students` (`sid`, `LEFT(name, 2)`, `bus`) VALUES
(51, 'Me', 'BusA'),
(52, 'Hi', 'BusA'),
(59, 'An', 'BusA'),
(70, 'Mi', 'BusB'),
(100, 'Yu', 'BusB');
CREATE TABLE `STATevent` (
`eventid` int(24) NOT NULL,
`sid` int(4) NOT NULL,
`date` datetime NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`level` enum('absent','bus') CHARACTER SET utf8 NOT NULL,
`color` varchar(10) NOT NULL,
`Percent` tinyint(5) NOT NULL,
`note` varchar(266) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `STATevent` (`eventid`, `sid`, `date`, `created`, `level`, `color`, `Percent`, `note`) VALUES
(43, 59, '2022-11-30 21:17:04', '2022-11-28 12:17:04', 'bus', 'red', 100, '');

学生可以选择不获得巴士服务，这显示为一个条目(如上面的eventid 43)。我可以得到"巴士学生"的列表，以及一个id来显示谁取消了服务，谁没有。

SELECT C.name, C.sid, O.sid AS 'bid', C.bus FROM students C
LEFT JOIN STATevent O ON  C.sid = O.sid
WHERE C.bus LIKE 'Bus%' AND C.active = 'Yes' ;

但是，当我尝试在中限制时，结果只显示取消服务的人。

SELECT C.name, C.sid, O.sid AS 'bid', C.bus FROM students C
LEFT JOIN STATevent O ON  C.sid = O.sid
WHERE C.bus LIKE 'Bus%' AND C.active = 'Yes' AND O.date LIKE '2022-11-29%';

我如何添加这个限制器并获得像第一个查询一样的完整结果?提前感谢您的帮助。

Answer 1

您可以将事件日期的限制移动到左连接的ON子句:

SELECT c.name, c.sid, o.sid AS bid, c.bus
FROM students c
LEFT JOIN STATevent o
ON o.sid = c.sid AND
DATE(o.date) = '2022-11-29'
WHERE c.bus LIKE 'Bus%' AND c.active = 'Yes';

对于没有取消服务的学生，bid将报告为null。

Answer 2

您希望显示使用公共汽车服务的所有学生，但排除那些选择在特定日期退出的学生。您可以使用NOT EXISTS或NOT IN来执行此查找。

SELECT *
FROM `students`
WHERE bus LIKE 'Bus%' AND active = 'Yes'
AND sid NOT IN
(
SELECT sid
FROM `STATevent`
WHERE DATE(date) = DATE '2022-11-30'
)
ORDER BY sid;

如果你想用一个标志来显示所有潜在的公交服务用户，不管他们当天是否出席，你可以把条件移到select子句:

SELECT s.*
sid IN
(
SELECT sid
FROM `STATevent`
WHERE DATE(date) = DATE '2022-11-30'
) AS opt_out
FROM `students` s
WHERE bus LIKE 'Bus%' AND active = 'Yes'
ORDER BY sid;

演示:https://dbfiddle.uk/TxyzF564