table: id code type create_timeselect t1.* ,t2.* from table t1 left join table t2 on t1.code = t2.code where t1.type = 0 and t2.type = 1
现在 code 一样,但是type为 1 有一条或者 很多条 但是 创建时间是不一样的,我只要 create_time最后的一条 去和type =0 的left
join请各位帮忙sql
现在 code 一样,但是type为 1 有一条或者 很多条 但是 创建时间是不一样的,我只要 create_time最后的一条 去和type =0 的left
join请各位帮忙sql
from tb a left join
(select id,code,type,row_number() over(partition by code order by create_time desc)
from tb where type=1) b
where a.type=0 and a.code=b.code
from tb a
where not exists(select 1 from tb b where b.type=1 and a.code=b.code and a.create_time<b.create_time) and a.type=0
select id,code,type from(select id,code,type,row_number() over(partition by code order by create_time desc) rn from tab where type=1
)where rn=1) a
left join(select * from tab where type=0) b
on a.code=b.code
select a.*,b.*
from tb a left join
(select id,code,type,row_number() over(partition by code order by create_time desc)
from tb where type=1) b
where a.type=0 and b.rn=1 and a.code=b.code