如何通过自连接检索同RegCode同日期（忽略时分）不同AdmissionID的记录

针对你遇到的这个需求——找出**相同RegCode、不同AdmissionID、AdmitDate日期部分一致（忽略时分）**的记录，同时解决日期判断不准和子查询别名无法复用的问题，我整理了几种适配不同数据库的实用方案：

核心思路 #

问题的根源在于：

1. 原AdmitDate包含时分信息，直接比较会误判日期相同但时间不同的记录；
2. SQL的执行顺序是FROM/JOIN → WHERE → GROUP BY → SELECT，所以SELECT里的别名无法在GROUP BY或连接条件中直接使用，得换方式复用日期提取的逻辑。

方案1：直接在连接条件中处理日期（适合所有主流数据库） #

这种方式最简单直接，在连接时对AdmitDate做日期截断，忽略时分秒的干扰。

MySQL示例

SELECT DISTINCT t1.*
FROM your_table t1
JOIN your_table t2
  ON t1.RegCode = t2.RegCode
  AND t1.AdmissionID != t2.AdmissionID
  AND DATE(t1.AdmitDate) = DATE(t2.AdmitDate)
ORDER BY t1.RegCode, DATE(t1.AdmitDate), t1.AdmissionID;

SQL Server示例

用CAST或CONVERT提取纯日期部分：

SELECT DISTINCT t1.*
FROM your_table t1
JOIN your_table t2
  ON t1.RegCode = t2.RegCode
  AND t1.AdmissionID != t2.AdmissionID
  AND CAST(t1.AdmitDate AS DATE) = CAST(t2.AdmitDate AS DATE)
ORDER BY t1.RegCode, CAST(t1.AdmitDate AS DATE), t1.AdmissionID;

PostgreSQL示例

用DATE()或TRUNC()函数截断日期：

SELECT DISTINCT t1.*
FROM your_table t1
JOIN your_table t2
  ON t1.RegCode = t2.RegCode
  AND t1.AdmissionID != t2.AdmissionID
  AND DATE(t1.AdmitDate) = DATE(t2.AdmitDate)
ORDER BY t1.RegCode, DATE(t1.AdmitDate), t1.AdmissionID;

说明：

• 加DISTINCT是避免同一组记录（比如Eric的三条）因为两两配对而重复出现；
• 如果想让每个配对只出现一次（比如只保留t1.AdmissionID < t2.AdmissionID的组合），可以把t1.AdmissionID != t2.AdmissionID改成t1.AdmissionID < t2.AdmissionID，这样就不需要DISTINCT了。

方案2：用CTE（公共表表达式）预处理日期，解决别名复用问题 #

如果觉得重复写日期函数麻烦，或者需要在多个地方复用日期部分，可以用CTE先把日期提取出来，后续直接引用别名：

跨数据库通用示例（支持CTE的数据库：MySQL8.0+/SQL Server/PostgreSQL等）

WITH date_processed AS (
  SELECT 
    AdmitDate,
    AdmissionID,
    RegCode,
    FirstName,
    -- 下面的函数根据你的数据库替换：
    -- MySQL: DATE(AdmitDate)
    -- SQL Server: CAST(AdmitDate AS DATE)
    -- PostgreSQL: DATE(AdmitDate)
    DATE(AdmitDate) AS AdmitDateOnly
  FROM your_table
)
SELECT DISTINCT t1.*
FROM date_processed t1
JOIN date_processed t2
  ON t1.RegCode = t2.RegCode
  AND t1.AdmissionID != t2.AdmissionID
  AND t1.AdmitDateOnly = t2.AdmitDateOnly
ORDER BY t1.RegCode, t1.AdmitDateOnly, t1.AdmissionID;

说明：

• CTE会先执行，生成包含AdmitDateOnly（仅日期部分）的临时数据集，后续的连接和排序都可以直接用这个别名，完美解决了之前别名无法复用的问题；
• 这种方式代码更简洁易维护，如果后续要修改日期处理逻辑，只需要改CTE里的一行即可。

验证结果 #

用你提供的测试数据执行上述SQL后，Eric的三条记录会被全部检索出来（符合相同RegCode、不同AdmissionID、日期部分一致的条件），其他不符合条件的记录会被正确过滤。

内容的提问来源于stack exchange，提问作者Shahad g