gpt4 book ai didi

sql - 连续行之间的日期差异 - 复杂

转载 作者:行者123 更新时间:2023-12-01 16:13:57 29 4
gpt4 key购买 nike

我之前发布过question已得到答复,但我也需要对此进行查询。我有一个包含这样数据的表结构(日期格式为 dd/mm/yyyy)。

ID    Account Number    Unit    Admit_Date    Disch_Date
1 1001 w32 01/04/2012
2 1002 w32 01/04/2012 01/04/2012
3 1001 ccu 03/04/2012
4 1001 w33 05/04/2012
5 1003 cicu 04/04/2012
6 1001 ccu 07/04/2012
7 1001 ccu 07/04/2012 10/04/2012
8 1003 w33 05/04/2012
9 1003 w33 05/04/2012 08/04/2012

基本上,该表涉及入住特定病房并在病房之间转移,然后最终在同一天或几天后出院的患者。查询的预期结果将是:

Account_Number                                 No. Of Days
1001 01/04/2012 03/04/2012 2
1001 03/04/2012 05/04/2012 2
1001 05/03/2012 07/04/2012 2
1001 07/04/2012 10/04/2012 3
1002 01/04/2012 01/04/2012 0
1003 04/04/2012 05/04/2012 1
1003 05/04/2012 08/04/2012 3

出院日期字段仅在患者出院时填写,因此我想计算患者每次移动日期之间的日期差,包括入院日期和出院日期。

我使用 MS Access 2003。

我希望有人能够帮助我解决这个问题。

最佳答案

过滤掉不相关的数据

对于任何复杂的查询,艺术的一部分是逐步构建查询,并进行测试。

我假设表名称是 PatientMovements 并且:

Given pairs of rows like ID = {6,7} and ID = {8,9}, it is correct to say that the row where the patient (account number), unit and admission date with null discharge date is ignored when there is also a record for the same patient, unit and admit date but a non-null discharge date.

因此,第一步是生成我们需要处理的行,从数据库中记录的表中过滤掉不相关的数据。这是两组数据的 UNION:

  1. 具有非空解除日期的那些行。
  2. 出院日期为空但没有相同帐户、单位和入院日期的行。

显然,UNION 的第一部分是:

SELECT * FROM PatientMovements WHERE DischargeDate IS NOT NULL

不太明显的是,UNION 的第二部分是:

SELECT *
FROM PatientMovements AS p1
WHERE DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)

现在您可以将它们合并到一个结果集中:

SELECT *
FROM PatientMovements
WHERE DischargeDate IS NOT NULL
UNION
SELECT *
FROM PatientMovements AS p1
WHERE DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)

您可以通过检查上面的查询是否返回 ID 为 1..5、7 和 9 的行来验证它。

警告:未经测试的代码。此答案中的 SQL 均未接近 DBMS,因此未经测试。

应用以前学到的经验教训

然后你可以应用从其他 question 学到的知识唯一的复杂之处是您必须将该查询写入两次,这很痛苦(除非 MS Access 2003 支持“WITH”子句或公用表表达式)。

<小时/>

But would there be no single query to obtain this required output?

当然,UNION 是单个查询。我想你可以这样写:

SELECT *
FROM PatientMovements
WHERE (DischargeDate IS NOT NULL)
OR (DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)
)

我无法立即想到一种更紧凑的查询方式。

<小时/>

将 UNION 构建为“另一个答案”

另一个问题的接受答案有两种可能的解决方案(经评论修改并重新格式化):

SELECT T1.ID, T1.AccountNumber, T1.Date, 
MIN(T2.Date) AS NextDate,
DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
FROM YourTable T1
JOIN YourTable T2
ON T1.AccountNumber = T2.AccountNumber AND T2.Date > T1.Date

或者:

SELECT ID, AccountNumber, Date, NextDate,
DATEDIFF("D", Date, NextDate) AS DaysDiff
FROM (SELECT ID, AccountNumber, Date,
(SELECT MIN(Date)
FROM YourTable T2
WHERE T2.AccountNumber = T1.AccountNumber
AND T2.Date > T1.Date
) AS NextDate
FROM YourTable T1
) AS T

正如评论中所述,问题中缺少表名会导致答案中出现不同的表名;我所说的 PatientMovements 在这个答案中被称为 YourTable。另一个区别是原始问题不包括数据中的 Unit 或 DischargeDate 列。但是,我给出的 UNION 查询提供了运行这些查询的相关数据,因此剩下要做的就是将 UNION 查询写入其他答案中以代替 YourTable。这导致:

SELECT T1.ID, T1.AccountNumber, T1.Date, 
MIN(T2.Date) AS NextDate,
DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
FROM (SELECT *
FROM PatientMovements
WHERE (DischargeDate IS NOT NULL)
OR (DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)
)
) AS T1
JOIN (SELECT *
FROM PatientMovements
WHERE (DischargeDate IS NOT NULL)
OR (DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)
)
) AS T2
ON T1.AccountNumber = T2.Accountnumber AND T2.Date > T1.Date

或者:

SELECT ID, AccountNumber, Date, NextDate,
DATEDIFF("D", Date, NextDate) AS DaysDiff
FROM (SELECT ID, AccountNumber, Date,
(SELECT MIN(Date)
FROM (SELECT *
FROM PatientMovements
WHERE (DischargeDate IS NOT NULL)
OR (DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)
)
) AS T2
WHERE T2.Accountnumber = T1.AccountNumber
AND T2.Date > T1.Date
) AS NextDate
FROM (SELECT *
FROM PatientMovements
WHERE (DischargeDate IS NOT NULL)
OR (DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)
)
) AS T1
) AS T

因此,只要您小心,并在片段中开发查询,然后将它们一致地组合起来,就可以驯服看起来最糟糕的查询。

通用表表达式

请注意,SQL 标准具有“公用表表达式”(CTE),又名“WITH 子句”,这可以使事情变得更容易。

WITH YourTable AS
(SELECT *
FROM PatientMovements
WHERE (DischargeDate IS NOT NULL)
OR (DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)
)
)
SELECT T1.ID, T1.AccountNumber, T1.Date,
MIN(T2.Date) AS NextDate,
DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
FROM YourTable T1
JOIN YourTable T2
ON T1.AccountNumber = T2.AccountNumber AND T2.Date > T1.Date

或者:

WITH YourTable AS
(SELECT *
FROM PatientMovements
WHERE (DischargeDate IS NOT NULL)
OR (DischargeDate IS NULL
AND NOT EXISTS
(SELECT *
FROM PatientMovements AS P2
WHERE P1.Account = P2.Account
AND P1.Unit = P2.Unit
AND P1.AdmitDate = P2.AdmitDate
AND P2.DischargeDate IS NOT NULL
)
)
)
SELECT ID, AccountNumber, Date, NextDate,
DATEDIFF("D", Date, NextDate) AS DaysDiff
FROM (SELECT ID, AccountNumber, Date,
(SELECT MIN(Date)
FROM YourTable T2
WHERE T2.AccountNumber = T1.AccountNumber
AND T2.Date > T1.Date
) AS NextDate
FROM YourTable T1
) AS T

使用 CTE 的主要优点之一是,优化器被明确告知表表达式在所有使用的地方都是相同的,而当它被多次写出时,它可能无法发现这种共性。另外,多次编写查询可能会导致两个“本应相同”的查询实际上由于编辑错误而略有不同; CTE 排除了这种可能性。当前环境下的另一个优势是,将 CTE 与其他问题的解决方案结合起来简直就是 child 子的游戏。

遗憾的是,MS Access 2003 不太可能支持 CTE。我分担你的痛苦;我使用的 DBMS 主要也没有。

关于sql - 连续行之间的日期差异 - 复杂,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10098570/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com