2个表的查询
t1:
ID,cDate,cMsg
7, '2007-09-10 ', 'a '
8, '2007-09-10 ', 'b '
9, '2007-09-10 ', 'c '

t2:
ID,cDate,cMsg
1, '2007-09-11 ', 'c '
2, '2007-09-12 ', 'a '
3, '2007-09-06 ', 'b '
4, '2007-09-08 ', 'c '
5, '2007-09-08 ', 'a '
6, '2007-09-09 ', 'a '
7, '2007-09-10 ', 'a '
8, '2007-09-10 ', 'b '
9, '2007-09-10 ', 'c '
10, '2007-09-13 ', 'c '
11, '2007-09-14 ', 'c '
12, '2007-09-15 ', 'a '
13, '2007-09-11 ', 'b '
14, '2007-09-09 ', 'c '

---
先取t1中第一条记录,1, '2007-09-10 ', 'a ',在t2找匹配的如x, '2007-09-09 ', 'a '   和   x, '2007-09-11 ', 'a '(10号的前后一天)
,如果存在x, '2007-09-09 ', 'a ',则继续看是否存在x, '2007-09-08 ', 'a ',存在则继续向前,不存在则结束,
同理如果存在x, '2007-09-11 ', 'a ',则继续看是否存在x, '2007-09-12 ', 'a ',存在则继续向后,不存在则结束,

特别说明如果不存在x, '2007-09-09 ', 'a ',就已经结束向前查找,即使是存在着x, '2007-09-08 ', 'a ',也不是匹配记录.

将t2中匹配的结果记录插入同结构表t3


再取出t1中第二条记录,重复在t2中查找,只是条件从cMsg的 'a '换成 'b '

将结果插入到t3

....


最后取出t1中最后一条记录,操作同上

结果插入t3


将t1,t3表合并成同结构的t4表


------解决方案--------------------
declare @i int
declare @cdate datetime
declare @cmsg varchar(8)
declare @id int
declare @j int
set @i=0
set @j=1
declare pursue_cursor cursor static scroll for select * from t1
open pursue_cursor
while(@i < @@cursor_rows)
begin
fetch next from pursue_cursor into @id,@cdate,@cmsg
while(@j> 0)
begin
if exists (select * from t2 where datediff(day,cdate,@cdate)=@j and cmsg=@cmsg)
begin
insert t3 select * from t2 where datediff(day,cdate,@cdate)=@j and cmsg=@cmsg
set @j=@j+1
end
else
break
end
set @j=1
while(@j> 0)
begin
if exists (select * from t2 where datediff(day,cdate,@cdate)=-@j and cmsg=@cmsg)
begin
insert t3 select * from t2 where datediff(day,cdate,@cdate)=-@j and cmsg=@cmsg
set @j=@j+1
end
else
break
end
set @j=1
set @i=@i+1
end
close pursue_cursor
deallocate pursue_cursor
insert t4 select * from t3
insert t4 select * from t3
select * from t3 order by cmsg asc,cdate
select * from t4 order by cmsg asc,cdate