数据库不支持row_number函数,如何实现相同的效果
--查询公共条款表数据量
select count(1) from gg_tk;
240
--查询条款代码,发现条款代码不唯一
select count(distinct gg_tk_dm) from gg_tk;
90
--查询单个条款代码
select gg_tk_dm,gg_tk_zt,update_time from gg_tk where gg_tk_dm='ZD00001' order by update_time desc;
gg_tk_dm gg_tk_zt update_time
ZD00001 1 2024-03-26 11:28:00
ZD00001 0 2024-03-26 11:27:35
ZD00001 0 2023-08-23 13:43:41
ZD00001 0 2023-05-30 10:09:12
ZD00001 0 2023-03-22 09:29:16
ZD00001 0 2023-02-08 10:27:09
ZD00001 0 2022-12-29 15:23:05
ZD00001 0 2022-12-28 15:20:58
--需要按条款代码分组,查询更新时间为最新在条款代码状态,用row_number函数来做是很方便的,但是该数据库不支持row_number函数
select gg_tk_dm,gg_tk_zt from (
select gg_tk_dm,gg_tk_zt,row_number(partiton by gg_tk_dm order by update_time desc) as rn from gg_tk
) t where rn=1;
SQL 错误 [1064] [42000]: Proxy ERROR: You have an error in your SQL syntax; check the manual that corresponds to your Mysql
--改成用关联的方式来实现row_number函数的效果
select t1.gg_tk_dm,t1.gg_tk_zt
from gg_tk t1
join (select gg_tk_dm,max(update_time) as update_time from gg_tk group by gg_tk_dm) t2
on t1.gg_tk_dm=t2.gg_tk_dm and t1.update_time=t2.update_time
gg_tk_dm gg_tk_zt
ZD00001 1