gdragon_21
gdragon_21
采纳率0%
2016-04-25 07:41 阅读 1.8k

新手求助:sql语句,从多个表中取出最新时间的记录,组成一个新表

图片说明

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • w172087242 little_how 2016-04-25 07:51

    mysql的实现:
    insert into zongbiao(id, name ,vd,co2,wendu,time) select t1.id,t1.name,t1.vd,t2.co2,t2.wendu,t2.time from biao1 t1 join biao2 t2 on(t1.id=t2.id)
    order by t2.time desc limit 1;

    点赞 评论 复制链接分享
  • w172087242 little_how 2016-04-25 08:09

    1.mysql和oracle通用版

    create table loginfo(
    id int primary key auto_increment, -- 主键
    userId int not null, -- 用户id
    loginTime timestamp -- 登录时间
    );
    insert into loginfo(userid, loginTime) values (1,'2015-01-12 14:36:20');
    insert into loginfo(userid, loginTime) values (1,'2015-01-13 14:36:20');
    insert into loginfo(userid, loginTime) values (1,'2015-01-13 14:46:20');
    insert into loginfo(userid, loginTime) values (2,'2015-01-12 14:36:20');
    insert into loginfo(userid, loginTime) values (2,'2015-01-13 14:36:20');
    insert into loginfo(userid, loginTime) values (3,'2015-01-12 14:36:20');
    insert into loginfo(userid, loginTime) values (4,'2015-01-12 14:36:20');
    insert into loginfo(userid, loginTime) values (4,'2015-01-13 14:36:20');
    insert into loginfo(userid, loginTime) values (4,'2015-01-14 14:36:20');
    insert into loginfo(userid, loginTime) values (4,'2015-01-15 14:36:20');
    insert into loginfo(userid, loginTime) values (5,'2015-01-12 14:36:20');
    insert into loginfo(userid, loginTime) values (5,'2015-01-13 14:36:20');

    -- 查询最后登录的时间
    select * from loginfo t1 where loginTime = (select max(loginTime) from loginfo t2 where t1.userid=t2.userid);
    -- 查询最早登录时间
    select * from loginfo t1 where loginTime = (select min(loginTime) from loginfo t2 where t1.userid=t2.userid);

    2.oracle去重思路
    可以利用oracle的rowid进行去重

    这样你就可以根据我的小例子来对机器id进行去重了。

    点赞 评论 复制链接分享

相关推荐