表A 名字TableA 具备以下字段:
----------------------------
id 学号 姓名 性别 班级
----------------------------
表B 名字TableB 具备以下字段:
----------------------------
id 学号 登录时间 登录IP 操作信息
----------------------------
TableA与TableB的对应关系是一对多,比方TableA有三条记录,在TableB中分别又对应了三条记录,也就TableB是9条记录。现在想显示 TableB中 登录时间最大的那条记录信息与TableA一并显示:
想显示成如下字段的三条记录:TableA.ID TableA.学号 TableA.姓名 TableA.性别 TableA.班级 TableB.登录时间 TableB.登录IP TableB.操作信息请问这个SQL数据该怎么写??
----------------------------
id 学号 姓名 性别 班级
----------------------------
表B 名字TableB 具备以下字段:
----------------------------
id 学号 登录时间 登录IP 操作信息
----------------------------
TableA与TableB的对应关系是一对多,比方TableA有三条记录,在TableB中分别又对应了三条记录,也就TableB是9条记录。现在想显示 TableB中 登录时间最大的那条记录信息与TableA一并显示:
想显示成如下字段的三条记录:TableA.ID TableA.学号 TableA.姓名 TableA.性别 TableA.班级 TableB.登录时间 TableB.登录IP TableB.操作信息请问这个SQL数据该怎么写??
(
select 1 id,'xh001' xh,'张三' name from dual
union all
select 2 id,'xh002' xh,'李四' name from dual
),
t2 as
(
select 1 id,'xh001' xh,date'2012-09-21' time,'192.168.100.100' ip from dual
union all
select 2 id,'xh001' xh,date'2012-09-23' time,'192.168.100.101' ip from dual
union all
select 3 id,'xh001' xh,date'2012-09-28' time,'192.168.100.102' ip from dual
union all
select 4 id,'xh002' xh,date'2012-09-22' time,'192.168.100.103' ip from dual
union all
select 5 id,'xh002' xh,date'2012-09-28' time,'192.168.100.104' ip from dual
union all
select 6 id,'xh002' xh,date'2012-09-24' time,'192.168.100.105' ip from dual
)select c.xh,c.name,d.time,d.ip
from t1 c,
(
select a.*
from t2 a,(select xh,max(time) time from t2 group by xh) b
where a.xh = b.xh and a.time = b.time
) d
where c.xh = d.xh
xh name time ip
---------------------------------------------------------------
1 xh001 张三 2012/9/28 192.168.100.102
2 xh002 李四 2012/9/28 192.168.100.104
(
select 1 id,'xh001' xh,'张三' name from dual
union all
select 2 id,'xh002' xh,'李四' name from dual
),
t2 as
(
select 1 id,'xh001' xh,date'2012-09-21' time,'192.168.100.100' ip from dual
union all
select 2 id,'xh001' xh,date'2012-09-23' time,'192.168.100.101' ip from dual
union all
select 3 id,'xh001' xh,date'2012-09-28' time,'192.168.100.102' ip from dual
union all
select 4 id,'xh002' xh,date'2012-09-22' time,'192.168.100.103' ip from dual
union all
select 5 id,'xh002' xh,date'2012-09-28' time,'192.168.100.104' ip from dual
union all
select 6 id,'xh002' xh,date'2012-09-24' time,'192.168.100.105' ip from dual
)select xh,name,time,ip
from (select t1.xh,t1.name,time,ip,row_number() over(partition by t2.xh order by t2.time desc ) rn
from t1,t2 where t1.xh = t2.xh
)
where rn = 1