在mysql中選擇至少有兩個[activity]間隔時間至少為24小時的[sth]-有解無憂

獲得了開發流行病爆發監測系統資料庫的最終專案。我想出了很多其他的東西，但是這個選擇陳述句我不知道如何寫：

列出時間視窗為2021-10-03 00:00至2021-10-05 00:00進行兩次病毒檢測的所有公民的電話號碼。兩次病毒檢測的間隔時間必須至少為 24 小時（至少相隔 24 小時）。

這是我的圖表的一部分，并且可能是這個特定問題所需的全部（忽略此處不需要的醫生圖表問題）

在mysql中選擇至少有兩個[activity]間隔時間至少為24小時的[sth]

我最初在想，也許每個 SSN 計算有多少個測驗 ID，然后檢查是否大于 2，但是如何計算 24 小時的時間……如果 3 個測驗之間有 20 小時和 8 小時，那么首先第三個是超過 24 ……無論如何，你希望能看到我的去向以及我的想法有多么錯誤，哈哈。任何幫助，將不勝感激：）

uj5u.com熱心網友回復：

可能是這個：

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

uj5u.com熱心網友回復：

首先，我的回答是基于我之前的評論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 小時）

以下是上述第一個陳述句的修改版本，它消除了在第一個值之后不到 24 小時出現第三個值的結果，即它只列出在下一次按時間順序發生的測驗之前有 24 小時或更長時間的記錄。

子查詢用于生成相隔不到 24 小時的兩次測驗的記錄Citizen。LEFT JOIN然后在可能包含由主查詢的第一個INNER JOIN（LEFT表）和子查詢（RIGHT表）生成的間隔小于 24 小時的記錄之間執行A。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' );

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/380029.html

標籤：mysql 数据库选择

上一篇：Python/MySQL-這種游標的使用有效，但是否正確？

下一篇：MySQL中每列的模式（不顯式寫列名）