我必须将列值填充到'y'如果条件为真,或者否则'n'。但是,就像在蜂巢中一样,它不支持子查询,以防万一,如何在蜂巢中返回。
(case
when exists
(select 1
from fntable fs
join dfntable dfs
on fs.id = dfs.id
and dfs.datetime =
(select max (cd.datetime)
from dfntable cd group by id)
and fs.s_id = dfs.s_id) then 'Y'
else 'N')"
使用带有分析函数的子查询 左联接。加入时,'y':
select case when cd.id is not null then 'Y' else 'N' end
from fntable fs
left join
( --group by cd.id, cd.s_id and filter
select cd.id, cd.s_id
from
( select max (cd.datetime) over (partition by dc.id) as max_id_datetime,
cd.id, cd.s_id, cd.datetime
from dfntable cd
) cd
where cd.datetime=max_id_datetime --filter only records with max date
group by cd.id, cd.s_id
) cd on fs.id = dfs.id and fs.s_id = dfs.s_id