table1为01,02,03学生消费明细表,users_id为学号,card_id为卡号,consume_date 为消费日期 , consume_time 为消费时间,balance为消费余额。table1
users_id card_id consume_date consume_time balance
001 001 07-01-10 12:00:00 10
001 001 07-01-10 18:00:00 5
001 001 07-01-10 19:00:00 3
002 002 07-01-11 08:00:00 20
002 002 07-01-12 11:00:00 16
002 002 07-01-13 18:00:00 12
003 003 07-01-10 07:00:00 18
003 003 07-01-11 12:00:00 15table2为01,02,03学生的对帐表,(users_id为学号,card_id为卡号)两字段和Table1表相同.X1,X2,X3,X4为帐余table2
users_id card_id X1 X2 X3 X4
001 001 -5 -5 -5 -5
002 002 -6 -6 -6 -6
003 003 4 4 4 4怎样把该学生table2 中的字段X1,X2,X3,X4替换为Table1表中该学生末比消费余额,也就是把table2表改成下表:table2
users_id card_id X1 X2 X3 X4
001 001 3 3 3 3
002 002 12 12 12 12
003 003 15 15 15 15(我使用的软件是PL/SQL developer 5.1.2)
求教>>>>>
users_id card_id consume_date consume_time balance
001 001 07-01-10 12:00:00 10
001 001 07-01-10 18:00:00 5
001 001 07-01-10 19:00:00 3
002 002 07-01-11 08:00:00 20
002 002 07-01-12 11:00:00 16
002 002 07-01-13 18:00:00 12
003 003 07-01-10 07:00:00 18
003 003 07-01-11 12:00:00 15table2为01,02,03学生的对帐表,(users_id为学号,card_id为卡号)两字段和Table1表相同.X1,X2,X3,X4为帐余table2
users_id card_id X1 X2 X3 X4
001 001 -5 -5 -5 -5
002 002 -6 -6 -6 -6
003 003 4 4 4 4怎样把该学生table2 中的字段X1,X2,X3,X4替换为Table1表中该学生末比消费余额,也就是把table2表改成下表:table2
users_id card_id X1 X2 X3 X4
001 001 3 3 3 3
002 002 12 12 12 12
003 003 15 15 15 15(我使用的软件是PL/SQL developer 5.1.2)
求教>>>>>
update table2
set x1=a.balance
from(
select t1.users_id,t1.card_id,a.balance
from(
select t1.users_id,t1.card_id,
max(cast(cast(t1.consume_date as varchar) + ' '+ cast(t1.consume_time as varchar) as datetime)) last_time
from table1 t1
group by cast(cast(t1.consume_date as varchar) + ' '+ cast(t1.consume_time as varchar) as datetime),t1.users_id,t1.card_id
) a
join table1 t1 on a.users_id=t1.users_id and a.card_id=t1.card_id
and cast(cast(t1.consume_date as varchar) + ' '+ cast(t1.consume_time as varchar) as datetime)=t1.last_time
) a
where card_id=a.card_id and users_id=a.users_id