选择在mysql中间隔时间至少为24小时的至少两个[活动]的[某物]

Question

最后得到一个大流行疫情监测系统数据库的开发项目。其他的事情我也很清楚，但是这个select语句我不知道怎么写：

列出所有在2021-10-03 00:00到2021-10-05 00:00做了两次病毒测试的公民的电话号码。两种病毒测试必须间隔至少24小时(至少24小时间隔)。

这是我的图表的一部分，也是这个问题所需要的一切(忽略这里不需要的医生图表问题)。

​

​

我最初在想，也许每个SSN都有多少个测试ID，然后检查是否大于2，但24小时的时间将如何计算.如果在第一次和第三次测试之间有20小时和8小时的3次测试超过24次.不管怎样，你可以看到我往哪里走，我想得有多错。如能提供任何帮助，将不胜感激:)

Answer 1

可能是这个：

SELECT a.ssn, MAX(a.phone) phone
FROM citizens a
JOIN patients b ON a.ssn = b.ssn
JOIN test c ON b.test_id = c.test_id
WHERE c.test_time BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00'
GROUP BY a.ssn
HAVING TIMESTAMPDIFF(HOUR, MIN(c.test_time), MAX(c.test_time)) >= 24

样本数据测试

CREATE TABLE citizens (
  ssn VARCHAR(20),
  phone VARCHAR(20)
);
CREATE TABLE patients (
  ssn VARCHAR(20),
  test_id INT
);
CREATE TABLE test (
  test_id INT,
  test_time DATETIME
);

INSERT INTO citizens VALUES ( 'A', '123' ), ( 'B', '456' ), ( 'C', '789' );
INSERT INTO patients VALUES ( 'A', 1 ), ( 'A', 2 ), ( 'A', 3 );
INSERT INTO patients VALUES ( 'B', 4 ), ( 'B', 5 ), ( 'B', 6 );
INSERT INTO patients VALUES ( 'C', 7 );

INSERT INTO test VALUES 
( 1, '2021-10-03 10:00:00' ),
( 2, '2021-10-03 20:00:00' ),
( 3, '2021-10-04 12:00:00' ),
( 4, '2021-10-03 10:00:00' ),
( 5, '2021-10-03 12:00:00' ),
( 6, '2021-10-03 19:00:00' ),
( 7, '2021-10-03 10:00:00' );

Answer 2

首先，我的回答是基于这样的假设：Patient应该按照我之前的评论被吸收到Test中。我也可以更新我的答案以包含列表结构的代码，也可以根据请求进行更新。

(下面的答案版本已经根据以下测试数据进行了测试)

回答查询(如果第三次测试可能发生在至少24小时间隔的两个测试之间)

首先，我将两个Test副本内部连接到自己，然后将结果内部连接到Citizen，这样，对于每个Citizen，在它们之间至少存在24小时的所有记录都会被返回，而不管它们之间是否出现了第三个记录，它们之间的距离还不到24小时。

生成的每条记录都应包含该匹配的测试时间(和测试ID)。我在这里选择了主题，以演示如何使用这些主题通知用户，并作为扩展声明的可能性的建议(如果用户希望扩展的话)。

不应该有重复的记录。

SELECT Citizen.ssn AS 'Social Security Number',
       Citizen.phone_number AS 'Phone Number',
       greater_than_first_table.test_id AS 'First Test ID',
       greater_than_first_table.test_time AS 'First Test Time',
       greater_than_second_table.test_id AS 'Second Test ID',
       greater_than_second_table.test_time AS 'Second Test Time'
FROM       Test AS greater_than_first_table
INNER JOIN Test AS greater_than_second_table ON greater_than_first_table.ssn = greater_than_second_table.ssn AND
                                                TIMESTAMPDIFF( HOUR,
                                                               greater_than_first_table.test_time,
                                                               greater_than_second_table.test_time ) >= 24
INNER JOIN Citizen ON greater_than_first_table.ssn = Citizen.ssn
WHERE greater_than_first_table.test_time  BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00' AND
      greater_than_second_table.test_time BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00';

回答查询(如上面所示，但只使用电话号码)

如果用户只对上述电话号码感兴趣，则可以使用以下内容。

GROUP BY用于消除重复结果。

SELECT Citizen.phone_number AS 'Phone Number'
FROM       Test AS greater_than_first_table
INNER JOIN Test AS greater_than_second_table ON greater_than_first_table.ssn = greater_than_second_table.ssn AND
                                                TIMESTAMPDIFF( HOUR,
                                                               greater_than_first_table.test_time,
                                                               greater_than_second_table.test_time ) >= 24
INNER JOIN Citizen ON greater_than_first_table.ssn = Citizen.ssn
WHERE greater_than_first_table.test_time  BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00' AND
      greater_than_second_table.test_time BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00'
GROUP BY Citizen.ssn;

回答查询(如果至少在下一次测试前24小时)

下面是第一个bove语句的修改版本，它消除了第一个语句后不到24小时出现第三个值的结果，即它只列出了在下一个按时间顺序进行的测试之前有24小时或更长时间的记录。

一个子查询用于为一个Citizen生成两个测试的记录，两个测试间隔不到24小时。然后，在主查询的第一个LEFT JOIN表( LEFT表)生成的可能包含不到24小时间隔的记录和来自子查询的记录( RIGHT表)之间执行LEFT。主查询中的最后一个WHERE子句从子查询所标识的结果中删除这些记录。

SELECT Citizen.ssn AS 'Social Security Number',
       Citizen.phone_number AS 'Phone Number',
       greater_than_first_table.test_id AS 'First Test ID',
       greater_than_first_table.test_time AS 'First Test Time',
       greater_than_second_table.test_id AS 'Second Test ID',
       greater_than_second_table.test_time AS 'Second Test Time'
FROM       Test AS greater_than_first_table
INNER JOIN Test AS greater_than_second_table ON greater_than_first_table.ssn = greater_than_second_table.ssn AND
                                                TIMESTAMPDIFF( HOUR,
                                                               greater_than_first_table.test_time,
                                                               greater_than_second_table.test_time ) >= 24
INNER JOIN Citizen ON greater_than_first_table.ssn = Citizen.ssn
LEFT JOIN 
(
    SELECT less_than_first_table.ssn       AS less_than_ssn,
           less_than_first_table.test_time AS less_than_test_time
    FROM       Test AS less_than_first_table
    INNER JOIN Test AS less_than_second_table ON less_than_first_table.ssn = less_than_second_table.ssn AND
                                                 TIMESTAMPDIFF( HOUR,
                                                                less_than_first_table.test_time,
                                                                less_than_second_table.test_time ) > 0 AND
                                                 TIMESTAMPDIFF( HOUR,
                                                                less_than_first_table.test_time,
                                                                less_than_second_table.test_time ) < 24

    WHERE less_than_first_table.test_time  BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00' AND
          less_than_second_table.test_time BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00'
) AS less_than_table ON greater_than_first_table.ssn = less_than_table.less_than_ssn AND
                        greater_than_first_table.test_time = less_than_table.less_than_test_time
WHERE greater_than_first_table.test_time  BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00' AND
      greater_than_second_table.test_time BETWEEN '2021-10-03 00:00:00' AND '2021-10-05 00:00:00' AND
      less_than_table.less_than_ssn IS NULL;

测试数据语句

DROP TABLE IF EXISTS Citizen;
DROP TABLE IF EXISTS Test;

CREATE TABLE Citizen
(
    ssn           VARCHAR( 50 )   NOT NULL,
    phone_number  VARCHAR( 50 )   NOT NULL,
    CONSTRAINT pkc_citizen              PRIMARY KEY ( ssn ),
    CONSTRAINT unc_citizen_phone_number UNIQUE      ( phone_number )
);

CREATE TABLE Test
(
    test_id     INT            NOT NULL   AUTO_INCREMENT,
    ssn         VARCHAR( 50 )  NOT NULL,
    test_time      DATETIME,
    CONSTRAINT pkc_test           PRIMARY KEY( test_id ),
    CONSTRAINT fkc_test_citizen   FOREIGN KEY ( ssn ) REFERENCES Citizen( ssn )
);

INSERT INTO Citizen VALUES ( 'THX-1138', '555-555-555' );
INSERT INTO Citizen VALUES ( 'TK4218',   '555-111-111' );
INSERT INTO Citizen VALUES ( 'R2-D2',    '555-222-222' );

INSERT INTO Test ( ssn, test_time ) VALUES ( 'THX-1138', '2021-10-02 22:34:51' );
INSERT INTO Test ( ssn, test_time ) VALUES ( 'THX-1138', '2021-10-03 09:00:00' );
INSERT INTO Test ( ssn, test_time ) VALUES ( 'THX-1138', '2021-10-03 10:00:00' );
INSERT INTO Test ( ssn, test_time ) VALUES ( 'THX-1138', '2021-10-04 11:34:51' );
INSERT INTO Test ( ssn, test_time ) VALUES ( 'TK4218',   '2021-10-03 10:34:51' );
INSERT INTO Test ( ssn, test_time ) VALUES ( 'TK4218',   '2021-10-04 11:34:51' );