Plan表
Plan_id Does_receive_data Receive_data_time
1
2
Plan_detail表
Plan_id Pda_receive_product_id Setting_time
1 1 2012.02
1 null 2012.05
2 1 2012.02
2 1 2012.03
写一个sql实现:若Plan_detail表中当相同的Plan_id时Pda_receive_product_id都不为null时,更新Does_receive_data为1, Receive_data_time为最大的Setting_time(plan_id为1,则为2012.05,plan_id为2,则为2012.03)
Plan_id Does_receive_data Receive_data_time
1
2
Plan_detail表
Plan_id Pda_receive_product_id Setting_time
1 1 2012.02
1 null 2012.05
2 1 2012.02
2 1 2012.03
写一个sql实现:若Plan_detail表中当相同的Plan_id时Pda_receive_product_id都不为null时,更新Does_receive_data为1, Receive_data_time为最大的Setting_time(plan_id为1,则为2012.05,plan_id为2,则为2012.03)
update a
set a.Does_receive_data = 1,
a.Receive_data_time = (select max(Setting_time) from from Plan_detail where Plan_id = a.Plan_id)
from Plan a
where not exists (select 1 from Plan_detail
where Plan_id = a.Plan_id and Pda_receive_product_id is null)
set Does_receive_data =case when not exists(select 1 from Plan_detail where Plan_id =plan.Plan_id and Pda_receive_product_id is not null) then 1 else 0 end),
Receive_data_time=(select max(Setting_time) from Plan_detail where Plan_id =plan.Plan_id )
(select max(Setting_time) from from Plan_detail where Plan_id = a.Plan_id)这里多了一个 FROM 注意