2个表的查询

Question

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表

Answer 1

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