（100分求解）超难题！查找在某时间段内的匹配记录
说明：表A是用户上下线记录，表B是用户在线期间的动作记录。
表A结构如下：
action ip time user
上线 21.12.34.7 2007-1-12 10：23：12 u1
上线 21.12.33.9 2007-1-12 10：23：17 u2
。。。。
下线 21.12.34.7 2007-1-12 16：20：15 u1
下线 21.12.33.9 2007-1-12 16：20：18 u2
。。。。
表B结构如下：
time srcip fromid destip toid
2007-1-12 11：24：12 21.12.34.7 f1 192.168.0.1 t1
2007-1-12 11：24：23 21.12.33.9 f2 192.168.0.4 t2
。。。。
2007-1-12 11：28：18 192.168.0.1 t1 21.12.34.7 f1
。。。。
2007-1-12 11：29：12 192.168.0.4 t2 21.12.33.9 f2
。。。。
请问高手，如何根据时间和ip找到某帐号在线期间所使用的id？
如上表，账号u1在线期间所使用的id为f1。
[解决办法]
declare @a table(action varchar(10), ip varchar(20), [time] smalldatetime, [user] varchar(10))
insert @a select '上线 ', '21.12.34.7 ', '2007-1-12 10:23:12 ', 'u1 '
union all select '上线 ', '21.12.33.9 ', '2007-1-12 10:23:17 ', 'u2 '
union all select '下线 ', '21.12.34.7 ', '2007-1-12 16:20:15 ', 'u1 '
union all select '下线 ', '21.12.33.9 ', '2007-1-12 16:20:18 ', 'u2 '
declare @b table(time smalldatetime, srcip varchar(20), fromid varchar(20), destip varchar(20), toid varchar(10))
insert @b select '2007-1-12 11:24:12 ', '21.12.34.7 ', 'f1 ', '192.168.0.1 ', 't1 '
union all select '2007-1-12 11:24:23 ', '21.12.33.9 ', 'f2 ', '192.168.0.4 ', 't2 '
union all select '2007-1-12 11:28:18 ', '192.168.0.1 ', 't1 ', '21.12.34.7 ', 'f1 '
union all select '2007-1-12 11:29:12 ', '192.168.0.4 ', 't2 ', '21.12.33.9 ', 'f2 '
select * from @b bb,
(select ip,[user], stime=(select min([time]) from @a where ip=a.ip and [user]=[a].[user] and action= '上线 '),xtime=(select min([time]) from @a where ip=a.ip and [user]=[a].[user] and action= '下线 ') from @a a group by ip,[user])
aa
where ip=bb.srcip and bb.[time] between stime and xtime
[解决办法]
CREATE TABLE A
([action] varchar(10),
ip varchar(20),
[time] smalldatetime,
[user] varchar(10))
INSERT INTO A
SELECT '上线 ', '21.12.34.7 ', '2007-1-12 10:23:12 ', 'u1 ' UNION ALL
SELECT '上线 ', '21.12.33.9 ', '2007-1-12 10:23:17 ', 'u2 ' UNION ALL
SELECT '下线 ', '21.12.34.7 ', '2007-1-12 16:20:15 ', 'u1 ' UNION ALL
SELECT '下线 ', '21.12.33.9 ', '2007-1-12 16:20:18 ', 'u2 '
CREATE TABLE B
(
[time] smalldatetime,
srcip varchar(20),
fromid varchar(20),
destip varchar(20),
toid varchar(10)
)
INSERT INTO B
SELECT '2007-1-12 11:24:12 ', '21.12.34.7 ', 'f1 ', '192.168.0.1 ', 't1 ' UNION ALL
SELECT '2007-1-12 11:24:23 ', '21.12.33.9 ', 'f2 ', '192.168.0.4 ', 't2 ' UNION ALL
SELECT '2007-1-12 11:28:18 ', '192.168.0.1 ', 't1 ', '21.12.34.7 ', 'f1 ' UNION ALL
SELECT '2007-1-12 11:29:12 ', '192.168.0.4 ', 't2 ', '21.12.33.9 ', 'f2 '
SELECT B.fromid,cc.stime,cc.etime FROM B INNER JOIN
--将每用户上线下线放至一行中
(SELECT aa.ip,aa.[time] stime,bb.[time] etime,aa.[user] FROM A aa INNER JOIN A bb
ON aa.ip=bb.ip AND aa.[action]= '上线 ' AND aa.[user]=bb.[user] AND bb.[action]= '下线 ') cc
ON
B.srcip=cc.ip AND B.[time] BETWEEN cc.stime AND cc.etime
DROP TABLE A
DROP TABLE B
--结果
fromid stime etime
-------------------- ----------------------- -----------------------
f1 2007-01-12 10:23:00 2007-01-12 16:20:00
f2 2007-01-12 10:23:00 2007-01-12 16:20:00
(2 行受影响)
[解决办法]
create table A([action] varchar(20), ip varchar(20), [time] datetime, [user] varchar(10))
insert A select '上线 ', '21.12.34.7 ', '2007-1-12 10:23:12 ', 'u1 '
union all select '上线 ', '21.12.33.9 ', '2007-1-12 10:23:17 ', 'u2 '
union all select '下线 ', '21.12.34.7 ', '2007-1-12 16:20:15 ', 'u1 '
union all select '下线 ', '21.12.33.9 ', '2007-1-12 16:20:18 ', 'u2 '
go
create table B([time] datetime, srcip varchar(20), fromid varchar(20), destip varchar(20), toid varchar(20))
insert B select '2007-1-12 11:24:12 ', '21.12.34.7 ', 'f1 ', '192.168.0.1 ', 't1 '
union all select '2007-1-12 11:24:23 ', '21.12.33.9 ', 'f2 ', '192.168.0.4 ', 't2 '
union all select '2007-1-12 11:28:18 ', '192.168.0.1 ', 't1 ', '21.12.34.7 ', 'f1 '
union all select '2007-1-12 11:29:12 ', '192.168.0.4 ', ' t2 ', '21.12.33.9 ', 'f2 '
select * from B
inner join
(
select tmpA.[user], tmpA.[ip],
上線時間=tmpA.[time],
下線時間=min(tmpB.[time])
from
(
select * from A
where [action]= '上线 '
)tmpA
left join
(
select * from A
where [action]= '下线 '
)tmpB on tmpA.ip=tmpB.ip and tmpA.[user]=tmpB.[user] and tmpA.[time] <=tmpB.[time]
group by tmpA.[user], tmpA.[ip], tmpA.[time]
)A on B.[time] between A.上線時間 and A.下線時間 and B.srcip=A.ip
