CREATE TABLE #temp2
(
 [MID] [int] ,
 [AID] [int] ,
 [ADATE] [nvarchar](20),
 [BID] [int] ,
 [BDATE] [nvarchar](20)
)
--------------------------------------------
 declare @tagint int -- first while repeat count
 declare @tmcount int --main table record count
 declare @tempMID int 
 -------------------------
 declare @taCount int 
 declare @taID int
 declare @tbCount int 
 declare @tbID int 
 declare @maxcount int
 ---------------------------
 declare @whilecount int
 -------------------------
 ---here is your new table constructor
 declare @taMid int
 declare @taDate nvarchar(20)
 declare @tbMid int
 declare @tbDate nvarchar(20)
 -----------------------------
 set @tagint =0
 select @tmcount =COUNT(MID) from TM  ---get total count 
set @tempMID =0
 while(@tagint<@tmcount)
   begin   
      select top 1 @tempMID =TM.MID from TM where MID >@tempMID
      set @taCount =0
      set @tbCount =0
      --select top 1 @tempMID =TM.MID from TM where MID not in(select top @tagint MID from TM order by TM.MID asc)
      select @taCount = count(distinct TA.AID) from TA where TA.MID =@tempMID   --获取a表中包含的合同数
      select @tbCount = count(distinct TB.BID) from TB where TB.MID=@tempMID  --获取b表中包含的合同数
      
      if(@taCount>@tbCount)begin set @maxcount =@taCount end --获取合同多的为循环数
      else begin set @maxcount=@tbCount end
      
      set @whilecount =0
      set @taID =0
      set @tbID =0
      while(@whilecount<@maxcount)
      begin  --start constructor new table data

      set @taDate =''
      set @tbDate=''
      select top 1 @taID=TA.AID,@taMid =TA.MID,@taDate =TA.ADATE from TA where TA.AID>@taID and TA.MID=@tempMID
      select top 1 @tbID=TB.BID,@tbMid =TB.MID,@tbDate =TB.BDATE from TB where TB.BID>@tbID and TB.MID=@tempMID
      insert into #temp2 (MID,AID,ADATE,BID,BDATE) values(@tempMID,@taID,@taDate,@tbID,@tbDate)
      set @whilecount =@whilecount +1
      end
      
     set @tagint =@tagint +1
    end
    select * from #temp2
drop table #temp2