- 论坛徽章:
- 0
|
嗯,看看这样行了吧? 自己感觉效率有点低,呵呵,等待版内各高手的答案
create table chk(
user varchar(8) not null,
ip char(15) not null
);
create table act(
user varchar(8) not null,
sttid char(15) not null,
time int not null
);
insert into act values
('tom','1.1.1.1',1),
('tom','1.1.1.6',2),
('tom','1.1.1.9',4),
('James','1.1.1.2',1),
('Peter','1.1.1.3',1),
('Joe','1.1.1.4',1);
insert into chk values
('tom',''),
('James',''),
('Peter',''),
('Robot','');
select * from act
where (user,time) in
(select user,max(time) from act group by user);
-- 这样select的话,保证选出来的子集都是“时间最大的用户纪录”
update chk as x
set ip=(select sttid from act as y
where x.user=y.user and (user,time) in
(select user,max(time) from act group by user)
)
where x.user in (select user from act);
|
还有,没有用楼主的命名实例,是为了少敲点键盘,就麻烦楼主自己改一改语句吧
[ 本帖最后由 fnems 于 2007-7-17 15:35 编辑 ] |
|