假設你 join 數張 table 得到的結果叫: result
;with tb1 as (
select t1.*,
case when t1.COLA = 'GOOD' then t1.ID else t2.ID end as COLB,
row_number() over (partition by t1.ID order by t2.ID desc) as num
from result t1 left join result t2
on t1.COLA != 'GOOD' and t2.COLA = 'GOOD' and t1.ID > t2.ID
)
select *
from tb1
where num = 1
※ 引述《Nowtreel (盜版陳金城)》之銘言：
: 資料庫-MSSQL-2012
: 各位好
: 資料庫新手想請教
: 目前組出一張表，有欄位ID ... COLA
: 是JOIN數張TABLE得到的結果
: 如下：
: ID ... COLA COLB
: