根據列比較選擇可能包含空值的行

給定一個名為 table1 的表，其前幾行如下所示：

Idkey DateA     DateB       FieldA    FieldB
1     '2011-06' '2011-03'   20        A
1     '2011-05' '2011-03'   20        A
1     '2011-04' '2011-03'   20        A
1     '2011-03' '2011-03'   20        A
1     '2011-02'  <null>     <null>    <null>
1     '2011-01'  <null>     <null>    <null>
2     '2011-08'  '2011-06'  30        B
2     '2011-07'  '2011-06'  30        B
2     '2011-06'  '2011-06'  30        B
2     '2011-05'  <null>     <null>    <null>

我想從 DateA = DateB 和 DateB 為 2011 年的那一刻開始選擇唯一 Idkeys 值的所有行。也就是說，我想：

Idkey DateA     DateB       FieldA    FieldB
1     '2011-03' '2011-03'   20        A
1     '2011-02'  <null>     <null>    <null>
1     '2011-01'  <null>     <null>    <null>
2     '2011-06'  '2011-06'  30        B
2     '2011-05'  <null>     <null>    <null>

我試過類似的東西：

with subt as (
 select Idkey from table1
 where DateB between '2011-01' AND '2011-12'
 group by Idkey 
 order by 1
)

select Idkey, DateA, DateB, FieldA, FieldB,
from table1 b
join subt 
on subt.Idkey = b.Idkey
where b.DateA <= nvl(DateB,b.DateA);

但它並沒有給我我想要的東西。有任何想法嗎？

您也可以嘗試基於視窗 MAX() 的解決方案：

WITH
 FirstMatches AS
 (
   SELECT
     *,
     MAX(CASE WHEN DateA = DateB AND DateB BETWEEN '2011-01' AND '2011-12' THEN DateA END)
       OVER (PARTITION BY Idkey) AS FirstDateA
   FROM
     table1
 )
SELECT
 Idkey,
 DateA,
 DateB,
 FieldA,
 FieldB
FROM
 FirstMatches
WHERE
 DateA <= FirstDateA
;

此解決方案假定“開始於”所暗示的行順序與ypercubeᵀᴹ 的解決方案DateA DESC相同。

請注意，雖然查詢將在DateB為 2011 年的行中查找具有匹配日期的行，但輸出可能包括DateA日期早於 2011 年的行。如果您只想獲取DateA為 2011 年的行，您可以將其指定為 CTE 中的 WHERE 過濾器，也可以在不再需要時刪除 CASE 表達式中DateB的範圍過濾器：

WITH
 FirstMatches AS
 (
   SELECT
     *,
     MAX(CASE WHEN DateA = DateB THEN DateA END)
       OVER (PARTITION BY Idkey) AS FirstDateA
   FROM
     table1
   WHERE
     DateA BETWEEN '2011-01' AND '2011-12'
 )
SELECT
 Idkey,
 DateA,
 DateB,
 FieldA,
 FieldB
FROM
 FirstMatches
WHERE
 DateA <= FirstDateA
;

我假設“第一個”是指當行按 . 排序時DateA DESC。然後，您需要Idkey使用簡單的 group by 查詢找到每個最大日期，然後重新加入表：

with first_match as
( select Idkey, max(DateA) as DateA 
 from table1
 where DateA = DateB 
 group by Idkey 
)
select t.*
from table1 as t
 join first_match as f
   on  t.Idkey = f.idkey
   and t.DateA <= f.DateA 
order by
   t.Idkey,
   t.DateA desc ;

引用自：https://dba.stackexchange.com/questions/149849