表a是海量视图,由n个结构相同的表组成,存储所有帐号的活动记录,且根据时间不断有新增,结构如下:
cpaturetime fromid toid
2007-3-1 1:12:10 a b
2007-3-1 1:12:10 c d
2007-3-1 1:12:11 e f
...
2007-3-1 1:12:20 b a
2007-3-1 1:12:30 d c
...表b是特定帐号列表,存储了几个特定帐号,不定期添加,结构如下:
username
a
b
...表c结构和表a相同,是根据表b中的特定账号从表a中提取的帐号活动记录(匹配fromid或toid),初始为空表。
要求如下:
1、首先根据表b搜寻表a中的记录(匹配fromid或toid)并插入表c(无重复记录);
2、如表b有新增,重复步骤1;
3、下次执行该语句时,如表a有新增,根据表b查询表a中的相关新增记录(匹配fromid或toid),并插入表c(无重复记录)。不知是否很复杂?
cpaturetime fromid toid
2007-3-1 1:12:10 a b
2007-3-1 1:12:10 c d
2007-3-1 1:12:11 e f
...
2007-3-1 1:12:20 b a
2007-3-1 1:12:30 d c
...表b是特定帐号列表,存储了几个特定帐号,不定期添加,结构如下:
username
a
b
...表c结构和表a相同,是根据表b中的特定账号从表a中提取的帐号活动记录(匹配fromid或toid),初始为空表。
要求如下:
1、首先根据表b搜寻表a中的记录(匹配fromid或toid)并插入表c(无重复记录);
2、如表b有新增,重复步骤1;
3、下次执行该语句时,如表a有新增,根据表b查询表a中的相关新增记录(匹配fromid或toid),并插入表c(无重复记录)。不知是否很复杂?
select distinct a.* from a,
(
select b.username from b
where not exists
(
select * from
(
select formid,toid from c
group by fromid,toid
)tmp
where b.username in(tmp.fromid,tmp.toid)
)
)tmp
where tmp.username in(a.fromid,a.toid)
create view c
as
select cpaturetime,fromid,toid from a inner join b on a.fromid=b.username
union
select cpaturetime,fromid,toid from a inner join b on a.toid=b.username
go
insert c
select distinct a.* from a,
(
select b.username from b
where not exists
(
select * from
(
select formid,toid from c
group by fromid,toid
)tmp
where b.username in(tmp.fromid,tmp.toid)
)
)tmp
where tmp.username in(a.fromid,a.toid)以下可解决第3个问题:
insert c
select distinct a.* from a
(
select c.fromid,c.toid,capturetime=max(c.capturetime) from c,b
where b.username in(c.fromid,c.toid)
group by c.fromid,c.toid
)tmp
where a.fromid=tmp.fromid and a.toid=tmp.toid and c.capturetime>tmp.capturetimelz试试。hehe
select distinct a.* from a
(
select c.fromid,c.toid,capturetime=max(c.capturetime) from c,b
where b.username in(c.fromid,c.toid)
group by c.fromid,c.toid
)tmp
where a.fromid=tmp.fromid and a.toid=tmp.toid and c.capturetime>tmp.capturetime这里不是很正确。
select distinct * from
(
select distinct a.* from a
(
select c.fromid,capturetime=max(c.capturetime) from c,b
where b.username=c.fromid
group by c.fromid
)tmp
where a.fromid=tmp.fromid and c.capturetime>tmp.capturetime
union
select c.toid,capturetime=max(c.capturetime) from c,b
where b.username=c.toid
group by c.toid
)tmp
where a.toid=tmp.toid and c.capturetime>tmp.capturetime
)tmp再试试。
select distinct * from
(
select a.* from a
(
select c.fromid,capturetime=max(c.capturetime) from c,b
where b.username=c.fromid
group by c.fromid
)tmp
where a.fromid=tmp.fromid and c.capturetime>tmp.capturetimeunionselect a.* from a
(
select c.toid,capturetime=max(c.capturetime) from c,b
where b.username=c.toid
group by c.toid
)tmp
where a.toid=tmp.toid and c.capturetime>tmp.capturetime
)tmp再试试。