2013-05-07黑山供暖导数据

--有意思的数据

select  charindex(char(10),usr_id) from t_zqt2

declare @a1 varchar(50)
declare @a2 varchar(50)
--有00单元00层的房间  00单元各层+1
declare cur_key1 cursor scroll for
select boiler,building from t_zqt2 where substring(roomidtext, 10,2)='00' and substring(roomidtext,13,2)='00'
group by boiler,building order by boiler,building
open cur_key1
 fetch next from cur_key1 into @a1,@a2
 WHILE @@FETCH_STATUS = 0
  begin
   update t_zqt2 set floor2 = right('0'+ convert(varchar(10),  (convert(int,floor2)+1) )   ,2)
   where boiler = @a1 and building = @a2
   fetch next from cur_key1 into @a1,@a2
  end
close cur_key1
deallocate cur_key1

--00单元楼上有住宅的

declare @a1 varchar(50)
declare @a2 varchar(50)
declare @a3 int
declare @b1 varchar(50)
declare @b2 int
declare @c1 varchar(50)
declare @i int
declare @parm varchar(10)
declare @ls_sql varchar(max)
declare cur_key1 cursor scroll for
select boiler,Building,count(*) from
 (
 select boiler,building,a1,MAX(a3) a4 from
  (
  select boiler,building, substring(roomidtext, 10,2) a1,substring(roomidtext,13,2) a2, count(*) a3
  from t_zqt
  where substring(roomidtext, 10,2)<>'00'
  group by boiler,building, substring(roomidtext, 10,2),substring(roomidtext,13,2)
  ) as t_a1
 group by boiler,building,a1
 ) as t_a2
group by boiler,building
order by boiler,building
open cur_key1
 fetch next from cur_key1 into @a1,@a2,@a3
 WHILE @@FETCH_STATUS = 0
 begin
  declare cur_key2 cursor scroll for
  select b1,MAX(b3) from
   (
   select boiler,building, substring(roomidtext, 10,2) b1,substring(roomidtext,13,2) b2, count(*) b3
   from t_zqt
   where substring(roomidtext, 10,2)<>'00' and boiler = @a1 and building = @a2
   group by boiler,building, substring(roomidtext, 10,2),substring(roomidtext,13,2)
   ) as t_b1
  group by boiler,building,b1
  open cur_key2 
   fetch next from cur_key2 into @b1,@b2
   WHILE @@FETCH_STATUS = 0
    begin
     declare cur_key3 cursor scroll for
     select roomidtext from t_zqt2
     where cell2 = '00' and floor2 = '04' and boiler = @a1 and building = @a2
     order by boiler,building,roomidtext
     open cur_key3
      fetch next from cur_key3 into @c1
      set @i = 1
      WHILE @@FETCH_STATUS = 0
       begin    
        set @parm = right('0'+convert(varchar(10),@i),2)
        set @ls_sql = 'UPDATE t_zqt2 SET cell2 = ''' + @b1 + ''',room2 = ''' + @parm +''' where roomidtext = '''+@c1+''''
    
        exec(@ls_sql)
        --fetch cur_key3 into @c1
        set @i = @i +1
        if convert(int,@b1) = @a3
         begin
          fetch cur_key3 into @c1
          continue
         end
        else
         if @i > @b2
          break
         else
          fetch cur_key3 into @c1
       end      
     close cur_key3
     deallocate cur_key3
     fetch next from cur_key2 into @b1,@b2     
    end
  close cur_key2
  deallocate cur_key2
  fetch next from cur_key1 into @a1,@a2,@a3  
 end
close cur_key1
deallocate cur_key1

--00单元楼上无住宅的

 declare @a1 varchar(50)
declare @a2 varchar(50)
declare @a3 int
declare @b1 varchar(50)
declare @b2 int
declare @c1 varchar(50)
declare @i int
declare @parm varchar(10)
declare @ls_sql varchar(max)
declare cur_key1 cursor scroll for
select boiler,Building,count(*) from
 (
 select boiler,building,a1,MAX(a3) a4 from
  (
  select boiler,building, substring(roomidtext, 10,2) a1,substring(roomidtext,13,2) a2, count(*) a3
  from t_zqt
  where substring(roomidtext, 10,2)='00'
  group by boiler,building, substring(roomidtext, 10,2),substring(roomidtext,13,2)
  ) as t_a1
 group by boiler,building,a1
 ) as t_a2
group by boiler,building
order by boiler,building
open cur_key1
 fetch next from cur_key1 into @a1,@a2,@a3
 WHILE @@FETCH_STATUS = 0
 begin
  declare cur_key2 cursor scroll for
  select b1,MAX(b3) from
   (
   select boiler,building, substring(roomidtext, 10,2) b1,substring(roomidtext,13,2) b2, count(*) b3
   from t_zqt
   where substring(roomidtext, 10,2)='00' and boiler = @a1 and building = @a2
   group by boiler,building, substring(roomidtext, 10,2),substring(roomidtext,13,2)
   ) as t_b1
  group by boiler,building,b1
  open cur_key2 
   fetch next from cur_key2 into @b1,@b2
   WHILE @@FETCH_STATUS = 0
    begin
     declare cur_key3 cursor scroll for
     select roomidtext from t_zqt2
     where cell2 = '00' and floor2 = '04' and boiler = @a1 and building = @a2
     order by boiler,building,roomidtext
     open cur_key3
      fetch next from cur_key3 into @c1
      set @i = 1
      WHILE @@FETCH_STATUS = 0
       begin    
        set @parm = right('0'+convert(varchar(10),@i),2)
        set @ls_sql = 'UPDATE t_zqt2 SET cell2 = ''' + '01' + ''',room2 = ''' + @parm +''' where roomidtext = '''+@c1+''''
    
        exec(@ls_sql)
        --fetch cur_key3 into @c1
        set @i = @i +1
        if convert(int,@b1) = @a3
         begin
          fetch cur_key3 into @c1
          continue
         end
        else
         if @i > @b2
          break
         else
          fetch cur_key3 into @c1
       end      
     close cur_key3
     deallocate cur_key3
     fetch next from cur_key2 into @b1,@b2     
    end
  close cur_key2
  deallocate cur_key2
  fetch next from cur_key1 into @a1,@a2,@a3  
 end
close cur_key1
deallocate cur_key1