表A(用户登录信息)userID logtime
1 10:00:00
1 12:00:00
1 14:00:00
2 10:00:00
2 10:00:00
2 10:00:00表B(用户计费信息)
userID opeatetime ip
1 10:00:01 192.168.1.31
1 12:00:01 192.168.1.32
1 14:00:01 192.168.1.33
2 10:00:01 192.168.1.34
2 12:00:01 192.168.1.35
2 14:00:01 192.168.1.36需要得到如下结果(从表B中得到opeatetime距表A中logintime最相近时间的IP)
userID logtime ip
1 10:00:00 192.168.1.31
1 12:00:00 192.168.1.32
1 14:00:00 192.168.1.33
2 10:00:00 192.168.1.34
2 10:00:00 192.168.1.35
2 10:00:00 192.168.1.36
1 10:00:00
1 12:00:00
1 14:00:00
2 10:00:00
2 10:00:00
2 10:00:00表B(用户计费信息)
userID opeatetime ip
1 10:00:01 192.168.1.31
1 12:00:01 192.168.1.32
1 14:00:01 192.168.1.33
2 10:00:01 192.168.1.34
2 12:00:01 192.168.1.35
2 14:00:01 192.168.1.36需要得到如下结果(从表B中得到opeatetime距表A中logintime最相近时间的IP)
userID logtime ip
1 10:00:00 192.168.1.31
1 12:00:00 192.168.1.32
1 14:00:00 192.168.1.33
2 10:00:00 192.168.1.34
2 10:00:00 192.168.1.35
2 10:00:00 192.168.1.36
解决方案 »
- 请问sql2000 如何用sql语言从远程数据库导数据到本地数据库
- 问一个简单的SQL语句?
- 每隔一短时间读取数据
- sql =: 这个是啥意思?
- 取触发器inserted幻表值的问题
- 一段SQL语句
- 有没有控件可以实现生成行号的功能?
- 我想问个问题,在sql中有一记录,记录一个编号,如果每年的这个编号都要重新记数,能不能让SQL在新年到来的时候重新记数
- 在SQL中将<字符型'1000'>转为<数值型1000>用哪一个函数,反之用哪一个函数?
- 高分求救!关于sql server 日志文件问题
- 帮忙写个sql语句 我想按分类读取相关分类的推荐文章,请高手帮忙,感激啊
- 请教rs.last()的正确使用
declare @a table(userID INT, logtime varchar(20))
insert @a select 1 ,'10:00:00'
union all select 1 ,'12:00:00'
union all select 1 ,'14:00:00'
union all select 2 ,'10:00:00'
union all select 2 ,'10:00:00'
union all select 2 ,'10:00:00' declare @b table(userID INT, opeatetime varchar(20), ip varchar(20))
insert @b select 1 ,'10:00:01', '192.168.1.31'
union all select 1 ,'12:00:01', '192.168.1.32'
union all select 1 ,'14:00:01', '192.168.1.33'
union all select 2 ,'10:00:01', '192.168.1.34'
union all select 2 ,'12:00:01', '192.168.1.35'
union all select 2 ,'14:00:01', '192.168.1.36' SELECT *,ip=(SELECT TOP 1 ip FROM @b WHERE userID=a.userId ORDER BY abs(datediff(ss,logtime ,opeatetime ))) FROM @a a--result
/*userID logtime ip
----------- -------------------- --------------------
1 10:00:00 192.168.1.31
1 12:00:00 192.168.1.32
1 14:00:00 192.168.1.33
2 10:00:00 192.168.1.34
2 10:00:00 192.168.1.34
2 10:00:00 192.168.1.34(所影响的行数为 6 行)
*/
select a.userid,a.logtime,b.ip from
(select *,row_number() over(order by userid asc) as aid from 表A) a
,
(select *,row_number() over(order by userid asc) as sid from 表B) b
where a.aid=b.sid
--推荐树上的鸟儿
declare @a table(userID INT, logtime varchar(20))
insert @a select 1 ,'10:00:00'
union all select 1 ,'12:00:00'
union all select 1 ,'14:00:00'
union all select 2 ,'10:00:00'
union all select 2 ,'10:00:00'
union all select 2 ,'10:00:00' declare @b table(userID INT, opeatetime varchar(20), ip varchar(20))
insert @b select 1 ,'10:00:01', '192.168.1.31'
union all select 1 ,'12:00:01', '192.168.1.32'
union all select 1 ,'14:00:01', '192.168.1.33'
union all select 2 ,'10:00:01', '192.168.1.34'
union all select 2 ,'12:00:01', '192.168.1.35'
union all select 2 ,'14:00:01', '192.168.1.36' select
a.userID
,a.logtime
,IP = (select top 1 ip from @b where userID = a.userID order by abs(datediff(second, a.logtime, opeatetime)))
from @a a