table_task
table_customercustomer 和 task 是 一对多的关系。一个custo对应多个task我现在想要把 task表 按inserttime desc顺序第一条的taskgoing字段值更新到customer表里的 lastgoingtask里。
update GR_P_Customer cu set F_LastTaskGoing =
(select ta.F_TaskGoing from GR_C_Task ta
where rownum = 1 and ta.F_Customer = cu.F_Key
order by ta.F_InsertTime desc)运行 “ORA-00907: 缺失右括号”
table_customercustomer 和 task 是 一对多的关系。一个custo对应多个task我现在想要把 task表 按inserttime desc顺序第一条的taskgoing字段值更新到customer表里的 lastgoingtask里。
update GR_P_Customer cu set F_LastTaskGoing =
(select ta.F_TaskGoing from GR_C_Task ta
where rownum = 1 and ta.F_Customer = cu.F_Key
order by ta.F_InsertTime desc)运行 “ORA-00907: 缺失右括号”
set F_LastTaskGoing = (select ta.F_TaskGoing
from GR_C_Task ta
where ta.F_Customer = cu.F_Key
and ta.F_InsertTime =
(select max(ta.F_InsertTime)
from GR_C_Task ta
where ta.F_Customer = cu.F_Key))
没有数据库,没有实测,楼主自己测试吧
思路就是,既然不能order by,就取最大的时间 然后只查询这一条 效果和逆排序取第一条一样
===============================================================
max 或者 min 不可以用吗?
所以直接用max函数是不行的先要用开窗的统计函数找出这种对应关系,如 select F_Customer, F_TaskGoing,F_InsertTime,
row_number() over(partition by F_Customer order by F_InsertTime desc) rn
from F_TaskGoing
有了这个对应关系就好办了:
update GR_P_Customer cu
set cu.F_LastTaskGoing = (
select ta.F_TaskGoing
from (
select F_Customer,
F_TaskGoing,F_InsertTime,
row_number() over(partition by F_Customer order by F_InsertTime desc) rn
from F_TaskGoing
) ta
where ta.F_Customer = cu.F_Key
and rn = 1
)