| |
Коллекция TSQL скриптов на все случаи жизни
Подбор пароля
к примеру, известно что пользователь 'логинпользователя' имеет пароль из 4 цифр,
с возможными предстоящими нулями.
решение:
use master
declare @i int
declare @res varchar (50)
declare @pwd table (pass char(4))
set @i = 1
while @i <= 9999
begin
insert @pwd (pass)
values(right('0000' + cast (@i as varchar (4)),4))
set @i = @i + 1
end
SELECT name, pass FROM sysxlogins
inner join @pwd
on pwdcompare(pass , password) = 1
and name = 'логинпользователя'
Циклы в графе
Задача:
В ориентированном графе нужно найти все вершины, входящие в циклы.
решение:
-- Переменная I для ограничения максимального количества
-- уровней вложенности в режиме отладки кода
declare @i int, @rc int
-- исходная таблица описывающая ребра графа код ребра, начальная, конечная точка
declare @Src table (R int identity (1,1) , sp int , ep int)
insert @src (sp,ep) values (1,3) --
двойной цикл
insert @src (sp,ep) values (3,2)
insert @src (sp,ep) values (2,1)
insert @src (sp,ep) values (3,5)
insert @src (sp,ep) values (5,4)
insert @src (sp,ep) values (4,2)
insert @src (sp,ep) values (4,12) --Соединяет два цикла
insert @src (sp,ep) values (12,13)
insert @src (sp,ep) values (13,6)
insert @src (sp,ep) values (6,7) -- одиночный цикл
insert @src (sp,ep) values (7,8)
insert @src (sp,ep) values (8,6)
insert @src (sp,ep) values (5,9) -- нециклическая ветка
insert @src (sp,ep) values (9,10)
-- оптимизация - удаление листьев и веток несоединяющих циклы
-- если цель только убедиться в наличии циклов (ошибок графа типа "дерево")
то такой чистки достаточно!!!
select @rc =1
while @rc <> 0
begin
delete from @src where ep not in (select S.sp from @src S)
set @rc = @@rowcount
end
-- select * from @src -- показывает что такой чистки недостаточно для
-- точного решения задачи т.к. Присутствуют sp = 12,13
-- таблица маршрутов
declare @Routes table (sp int, ep int, r int)
-- Вставляем начальные данные
insert @Routes (sp, ep, r)
select sp, ep, r
from @src
select @i = 0, @rc =1
-- вставяем все маршруты для каждой точки sp исключая имеющиеся
while @rc <> 0
-- and @i < 8 -- в режиме отладки
begin
insert @Routes (sp, ep, r)
select NR.sp, NR1.ep , NR1.r
from @routes NR inner join @Routes NR1 on NR.ep = NR1.sp
where NR1.r not in (select WR.r from @routes WR where NR.sp = WR.sp
and NR1.ep = WR.ep )
set @rc = @@rowcount
set @i = @i +1
end
-- Результат: вершины циклов
select distinct sp
from @routes r
where r.sp = r.ep
Записи в строку с разделителями
решение(2005):
-- тестовые данные
declare @T TABLE(ID INT , S VARCHAR(8000))
INSERT @T(ID,S) VALUES(1,'первое')
INSERT @T(ID,S) VALUES(2,'второе')
INSERT @T(ID,S) VALUES(3,'третье')
--преобразование нескольких записей в список
select left(dd,len(dd)-2) as result
from (
select(
select s + ', ' 'data()'
from @T
order by ID
for xml path ('')) dd
) cc
Cтрока с разделителями в набор записей
Табличная функция (2000-2005):
CREATE FUNCTION [dbo].[iter_charlist_to_table] (@list ntext, @delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOTNULL,
str varchar(4000), nstr nvarchar(2000) COLLATE SQL_Latin1_General_CP1251_CI_AS )
AS
BEGIN
DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1 SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, charindex(@delimiter, @tmpstr) - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END
Рекурсивные функции CTE для работы с деревьями (2005):
create table TreeTable (id int, ParentId int )
insert TreeTable values(1, null )
insert TreeTable values(2, 1 )
insert TreeTable values(3, 2 )
insert TreeTable values(4, 3 )
insert TreeTable values(5, 4 )
insert TreeTable values(6, null )
insert TreeTable values(7, 6 )
go
-- нода и все предки
WITH T (Id, ParentId, nLevel) AS
( SELECT Id, ParentId, 0 nLevel FROM TreeTable (NOLOCK) WHERE id=4
UNION ALL
SELECT t1.Id, t1.Parentid, nLevel - 1
FROM TreeTable T1 (NOLOCK) JOIN T ON T.ParentId=T1.Id
) SELECT * FROM T
go
-- нода и потомки
WITH T (Id, ParentId, nLevel) AS
( SELECT Id, ParentId, 0 nLevel FROM TreeTable (NOLOCK) WHERE id=4
UNION ALL
SELECT t1.Id, t1.Parentid, 1 + nLevel
FROM TreeTable T1 (NOLOCK) JOIN T ON T1.ParentId=T.Id
) SELECT * FROM T
go
-- все потомки и предки ноды
WITH T (Id, ParentId, nLevel) AS
( SELECT Id, ParentId, 0 nLevel FROM TreeTable (NOLOCK) WHERE id=4
UNION all
SELECT t1.Id, t1.Parentid, 1 + nLevel
FROM TreeTable T1 (NOLOCK) JOIN T ON T1.ParentId=T.Id and t.nLevel >= 0
UNION all
SELECT t1.Id, t1.Parentid, nLevel -1
FROM TreeTable T1 (NOLOCK) JOIN T ON T.ParentId=T1.Id and t.nLevel <= 0
) SELECT distinct * FROM T
коллекция функций tsql для работы со строками:
коллекция Никифорова Игоря
| |
