Oracle SQL中隐式Outer Apply及Top 1使用方法咨询

需求说明 #

需要实现两个核心目标：

• 使用类似隐式APPLY的逻辑，直接引用FROM子句表的列，后续查询逻辑可复用处理后的结果，无需依赖临时表、CTE或嵌套子查询
• 在OUTER APPLY场景中结合“取最新1条记录”的逻辑

适配后的Oracle SQL代码 #

1. 创建临时表（替代SQL Server本地临时表） #

Oracle使用全局临时表存储临时数据，分为事务级（提交后清空）和会话级（会话结束后清空），此处采用事务级：

-- 创建用户临时表
CREATE GLOBAL TEMPORARY TABLE temp_users (
    Username VARCHAR2(10),
    Password1 INT
) ON COMMIT DELETE ROWS;

-- 创建登录尝试临时表
CREATE GLOBAL TEMPORARY TABLE temp_users_login_attempts (
    Username VARCHAR2(10),
    Password1 INT,
    AttemptDate DATE
) ON COMMIT DELETE ROWS;

2. 插入测试数据 #

Oracle需用TO_DATE函数将字符串转为日期类型：

INSERT INTO temp_users VALUES (' MAllen', 123), ('SEllis ', 124), ('MChen ', 126);

INSERT INTO temp_users_login_attempts VALUES
    ('MAllen ', 123, TO_DATE('20221001', 'YYYYMMDD')),
    (' SEllis ', 124, TO_DATE('20221001', 'YYYYMMDD')),
    (' MChen  ', 126, TO_DATE('20221001', 'YYYYMMDD')),
    ('MAllen ', 126, TO_DATE('20221008', 'YYYYMMDD')),
    (' SEllis ', 123, TO_DATE('20221008', 'YYYYMMDD')),
    (' MChen', 128, TO_DATE('20221008', 'YYYYMMDD'));

3. 核心查询（适配OUTER APPLY逻辑） #

Oracle 12c及以上版本原生支持OUTER APPLY，将原SQL的TOP 1替换为Oracle的FETCH FIRST 1 ROW ONLY语法即可：

SELECT t.*, u.*
FROM temp_users t
OUTER APPLY (
    SELECT LTRIM(RTRIM(t.username)) AS UserNameTrim
) unt
OUTER APPLY (
    SELECT ula.*
    FROM temp_users_login_attempts ula
    WHERE LTRIM(RTRIM(ula.UserName)) = unt.UserNameTrim
    ORDER BY ula.AttemptDate DESC
    FETCH FIRST 1 ROW ONLY
) u;

关键说明 #

1. APPLY语法支持：Oracle 12c开始引入APPLY系列语法，功能与SQL Server一致：
   • CROSS APPLY：仅返回主表与子查询匹配的记录（类似INNER JOIN）
   • OUTER APPLY：返回主表所有记录，子查询无匹配时返回NULL（类似LEFT JOIN）
2. 取最新记录：用FETCH FIRST 1 ROW ONLY替代SQL Server的TOP 1，结合ORDER BY可确保取到最新的登录尝试记录
3. 结果复用：第一个OUTER APPLY处理用户名去空格后，后续OUTER APPLY可直接引用unt.UserNameTrim，无需重复编写去空格逻辑

内容的提问来源于stack exchange，提问作者Spencer Puente