oracle sql经验

一对多取第一条数据

1. 通过 ROW_NUMBER() OVER (PARTITION BY XXX） 方式，当有数据 birthday 为 null 时，查询无法达到预期。

-- 想要拿 BBB 表 INP_DATE 最小的 BIRTHDAY
select *
from (select a.account_address                                                      "account_address",
             ''                                                                       "account_city",
             to_char(b.BIRTHDAY, 'yyyy-MM-dd')                                      "birthday",
             ROW_NUMBER() OVER (PARTITION BY b.BIRTHDAY ORDER BY b.INP_DATE DESC) rn
      from AAA a
               left join BBB b on b.a_id = a.id
      where opi.ES_EXCHANGE_FLAG = 0) t
where t.rn <= 1

2. 通过 fetch next 方式，需要 distinct 去重，会有一定的性能损失，但是这个支持 birthday 为 null 的数据。

select distinct a.account_address                                 "account_address",
       (SELECT to_char(b.BIRTHDAY, 'yyyy-MM-dd')
        FROM BBB b
        WHERE b.a_id = a.id
        ORDER BY b.INP_DATE DESC fetch first 1 row only) "birthday"
from AAA a