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

数据表的结构：
旅客身份证号订票时间目的地
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 无锡

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);

可以沿着这个思路去写SQL，把订了南京和订了无锡的记录分别拿出来做一个关联，同时要把时间转换年月日，然后关联条件就是两个表的身份证一样且日期一样

不知道你这个问题是否已经解决, 如果还没有解决的话:

帮你找了个相似的问题, 你可以看下: https://ask.csdn.net/questions/844642

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^