Oracle中求两次住院小于3天

在Oracle数据库查询中，如果你想找出那些住院天数小于3天的记录，并且这个操作需要做两次（可能是对不同的字段或者条件），你可以使用UNION ALL或者CASE WHEN语句结合GROUP BY和HAVING子句来实现。这里假设有一个名为"Hospitals"的表，其中包含"PatientID", "AdmissionDate", "DischargeDate"等字段。

-- 示例一：如果想分别按每个患者的住院记录找小于3天的
SELECT PatientID, MIN(DATEDIFF('Day', AdmissionDate, DischargeDate)) AS DaysStayed
FROM Hospitals
WHERE DATEDIFF('Day', AdmissionDate, DischargeDate) < 3
GROUP BY PatientID;

UNION ALL

SELECT PatientID, MIN(DATEDIFF('Day', AdmissionDate2, DischargeDate2)) AS DaysStayed
FROM (
    SELECT PatientID, AdmissionDate AS AdmissionDate2, DischargeDate AS DischargeDate2
    FROM Hospitals
)
WHERE DATEDIFF('Day', AdmissionDate2, DischargeDate2) < 3
GROUP BY PatientID;

-- 如果你想找到所有住院天数累计小于6天的患者
SELECT PatientID, SUM(CASE WHEN DATEDIFF('Day', AdmissionDate, DischargeDate) < 3 THEN 1 ELSE 0 END) AS TotalDaysLessThan3
FROM Hospitals
GROUP BY PatientID
HAVING SUM(CASE WHEN DATEDIFF('Day', AdmissionDate, DischargeDate) < 3 THEN 1 ELSE 0 END) > 0;
-- 这里是将每次住院小于3天计为1，累加后筛选出总天数少于3天的次数大于0的记录。

--