sql查询：旅客同一天既订了去南京的票，也定了去无锡的票，这样的旅客一共有对少人？

Question

数据表的结构：
旅客身份证号 订票时间 目的地
111 2023040108:00:51 南京
222 2023040108:00:51 上海
333 2023040108:00:51 无锡
111 2023040108:00:51 无锡
222 2023040108:00:51 无锡
333 2023040108:00:51 南京
444 2023040608:00:51 南京
444 2023040108:00:51 无锡

Answer 1

CREATE TABLE ticket
(
    id            INT                NOT NULL,
    buytime        DATETIME        NOT NULL,
    place        VARCHAR(10)     NOT NULL
);
INSERT INTO ticket (id,buytime,place) VALUE (111,20230401080051,'南京');
INSERT INTO ticket (id,buytime,place) VALUE (222,20230401080051,'上海');
INSERT INTO ticket (id,buytime,place) VALUE (333,20230401080051,'无锡');
INSERT INTO ticket (id,buytime,place) VALUE (111,20230401080051,'无锡');
INSERT INTO ticket (id,buytime,place) VALUE (222,20230401080051,'无锡');
INSERT INTO ticket (id,buytime,place) VALUE (333,20230401080051,'南京');
INSERT INTO ticket (id,buytime,place) VALUE (444,20230406080051,'南京');
INSERT INTO ticket (id,buytime,place) VALUE (444,20230401080051,'无锡');

SELECT COUNT(s1.id)/2 AS people_num FROM ticket AS s1
LEFT OUTER JOIN ticket AS s2
ON s1.id=s2.id AND s1.place<>s2.place 
AND s1.place IN ('南京','无锡') AND s2.place IN ('南京','无锡')
WHERE s2.id IS NOT NULL AND DATEDIFF(s1.buytime,s2.buytime) IN (0);