回傳左連接中的最后一個非空值

我有一個這樣的查詢

select
  a.date_field,
  b.date_field,
  b.interested_data
from tbl_dates a
  left join tbl_data b
     on a.date_field = b.date_field

這給了我一個結果集，如：

a_date_field | b_date_field | b_interested_data
2022-01-01   | 2022-01-01   | data_1
2022-01-02   | 2022-01-02   | data_2
2022-01-03   | null         | null

是否可以在第 3 行回傳 b_date_field 和 b_interested_data 的最后一個非空值？理想情況下，結果應該是

a_date_field | b_date_field | b_interested_data
2022-01-01   | 2022-01-01   | data_1
2022-01-02   | 2022-01-02   | data_2
2022-01-03   | 2022-01-02   | data_2

我知道 b_date_field 看起來像是一個錯誤，但我只對 b_interested_data 感興趣。

uj5u.com熱心網友回復：

基本上我認為有兩種方法。一種是使用apply，一種是使用last_value。我準備了一個帶有兩個選擇的小插入，它們回傳同一個表，所以選擇你最喜歡的一個（編輯：選擇一個適合你的解決方案并且在你的情況下具有最佳性能的那個）。

select
    *
into #dates
from (values (1,cast('20220101' as date)),(2,cast('20220102' as date)),(3,cast('20220103' as date))) a(date_id,[date])
;

select
    *
into #data
from (values ('data_1',cast('20220101' as date)),('data_2',cast('20220102' as date))) b(interested_data,[date])
;

--Solution 1
select distinct
      last_value(a.date) over (partition by a.date order by b.date rows between current row and unbounded following)
    , last_value(b.date) over (partition by a.date order by b.date rows between current row and unbounded following)
    , last_value(b.interested_data) over (partition by a.date order by b.date rows between current row and unbounded following)
from #dates a
left join #data b on
    b.date <= a.date
;

--Solution 2
select
    a.date
    , sub.date as b_date
    , sub.interested_data
from #dates a
outer apply (
    select top 1
          b.date as date
        , b.interested_data as interested_data
    from #data b
    where b.date <= a.date
    order by b.date desc
) as sub

uj5u.com熱心網友回復：

也許您可以使用APPLY運算子來完成。從表 b 中獲取“最后”行date_field

select
  a.date_field,
  b.date_field,
  b.interested_data
from tbl_dates a
  cross apply
  (
      select TOP (1) b.date_field, b.interested_data
      from  tbl_data b
      where a.date_field >= b.date_field
      order by b.date_field desc
  ) b

uj5u.com熱心網友回復：

使用lag()并且coalese()我們可以做到：假設 a_date_Field 是我們可以用來確定“先驗”值的順序。

WITH CTE AS (SELECT '2022-01-01' a_date_field, '2022-01-01' b_date_field, 'data_1' b_interested_data UNION ALL
SELECT '2022-01-02',   '2022-01-02',   'data_2' UNION ALL
SELECT '2022-01-03',    null,          null)
SELECT a_date_Field, 
       coalesce(B_Date_Field,lag(B_date_Field) over (order by a_date_Field)), 
       coalesce(B_Interested_Data,lag(B_Interested_Data) over (order by a_date_Field)) FROM CTE

給我們：

 -------------- ------------------ ------------------ 
| a_date_Field | (No column name) | (No column name) |
 -------------- ------------------ ------------------ 
| 2022-01-01   | 2022-01-01       | data_1           |
| 2022-01-02   | 2022-01-02       | data_2           |
| 2022-01-03   | 2022-01-02       | data_2           |
 -------------- ------------------ ------------------ 

uj5u.com熱心網友回復：

感謝大家。我從您的回答中意識到，連接條件也可以是>=or<=而不僅僅是=。這是我想出的解決方案：

drop table if exists #dates;
select
    *
into #dates
from (values (1,cast('20220101' as date)),(2,cast('20220102' as date)),(3,cast('20220103' as date))) a(date_id,[date])
;


drop table if exists #data;
select
    *
into #data
from (values ('data_1',cast('20220101' as date)),('data_2',cast('20220102' as date))) b(interested_data,[date])
;


select 
    ab.a_date,
    ab.b_date,
    ab.interested_data
from (
    select
        a.date a_date,
        b.date b_date,
        b.interested_data,
        row_number() over (
            partition by
                a.date
            order by
                a.date,
                b.date desc
        ) rn
    from #dates a
        left join #data b
            on a.date >= b.date
) ab
where
    ab.rn = 1
;

標籤：sql服务器

上一篇：找不到多識別符號錯誤訊息？

下一篇：在SQL中查找完全匹配