Распаковка номеров — различия между версиями

Версия 09:12, 12 марта 2014

Наверх

Запрос позволяет найти такой внутренний номер у пользователя, в котором

• не должно быть других пользователей или номеров ссылающихся на других пользователей;
• могут быть внешние номера, линии, быстрые номера.

Задача актуальна при поиске номера со структурой "пользователь + внешний телефон".

В качестве примера запрос находит номер у пользователя 1.

	if exists ( select * from tempdb..sysobjects where id=OBJECT_ID ( 'tempdb..#t' )) Drop table #t
	if exists ( select * from tempdb..sysobjects where id=OBJECT_ID ( 'tempdb..#temp' )) Drop table #temp
	if exists ( select * from tempdb..sysobjects where id=OBJECT_ID ( 'tempdb..#removed' )) Drop table #removed
	Create table #t ( nid uniqueidentifier, reactid uniqueidentifier )
	Create table #temp ( id uniqueidentifier, oldrid uniqueidentifier, newrid uniqueidentifier )
	Create table #removed ( nid uniqueidentifier, reactid uniqueidentifier )
	
	Insert Into #t
		Select np.Id, rr.ReactId
		From A_NumberPlan np
			Inner Join A_NumberPlanAction na on na.NumId = np.Id
			Inner Join A_Rules r on na.ExtraId = r.Id
			Inner Join A_RuleRecords rr on rr.RuleId = r.Id
		Where na.NumType = 0 

	Declare @cnt int, @rowcount int, @msg int
	Select @cnt = 0, @rowcount = 1
	
	While ( @rowcount > 0 ) and ( @cnt < 10 )
	begin
		Set @cnt = @cnt + 1
	
		--ссылки на номера @cnt - уровня
		Insert Into #temp
			Select t.nid, t.reactid, t1.reactid
			From #t t
				 Inner Join #t t1 on ( t.Reactid = t1.nId ) and ( t1.nId != t.nId ) and ( t1.reactid != t.nId )
			Where t.ReactId != t.Nid
				and not exists ( select nid from #removed r where t.nid = r.nid and t1.reactid = r.reactid )
				and not exists ( select nid from #t r where t.nid = r.nid and t1.reactid = r.reactid )
			Group by t.nid, t.reactid, t1.reactid
	
		-- 	
		Insert into #removed ( nid, reactid )
			Select t.nid, t.reactid
			From #t t
				Inner join #temp tmp on t.nid= tmp.id and t.reactid = tmp.oldrid
			Where not exists ( select nid from #removed r where t.nid = r.nid and t.reactid = r.reactid )
			Group by t.nid, t.reactid
							
		--Удаляем то что будем менять
		Delete From #t
			From #temp
		Where nid = id and reactid = oldrid
	
		--вставляем замены
		Insert Into #t ( nid, reactid )
			Select id, newrid From #temp


		--чистим времянку
		Select @rowcount = COUNT ( * ) 
		From #temp
				
		Truncate table #temp
	end
		
	-- удаляем остатки ссылок на номера
	Delete From #t
		From A_NumberPlan np
	Where reactid = np.Id
	
		
	--select np.Prefix, u.login from #t t inner join A_Users u on t.reactid=u.ID 
	--inner join A_NumberPlan np on t.nid=np.ID 
	--order by prefix
	--where login='Пользователь1'
	
	select t1.prefix from 
	(select prefix from 
	(
	select np.Prefix, u.login from #t t inner join A_Users u on t.reactid=u.ID 
	inner join A_NumberPlan np on t.nid=np.ID 
	)t
	group by Prefix having COUNT (*) = 1 )t1
	inner join 
	(
	select np.Prefix, u.login from #t t inner join A_Users u on t.reactid=u.ID 
	inner join A_NumberPlan np on t.nid=np.ID 
	)
	t2
	on t1.Prefix=t2.Prefix and t2.Login='Пользователь1'