Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Получить корневой элемент  [new]
Luzerka
Member

Откуда:
Сообщений: 20
CREATE TABLE Tree (Id int not null, MainId int null)
1 null
2 1
3 2
4 3

Мне нужно по идентификатору 4, получить рутовый идентификатор 1, в найденых мной примерах выводят все структуры целиком, но я не могу сообразить как это переделать. Помогите пожалуйста.
7 апр 12, 18:02    [12380822]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
cte tree parent child - вот список ключевых слов для поиска.
7 апр 12, 18:13    [12380856]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
declare @Id int = 4;

with cte
as
(
select 1 as level, * from Tree t
where Id = @Id
union all
select c.level + 1, t.* from Tree t
  inner join cte c
    on t.ID = c.MainID
)
select top 1 id, mainid from cte order by level desc
7 апр 12, 18:16    [12380868]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Luzerka
Member

Откуда:
Сообщений: 20
Спасибо большое, Алексей.
Работает!
Теперь пытаюсь понять, как это использовать для условия.
допустим запрос:
SELECT *
FROM Free AS f
WHERE
(f.SomeId IS NOT NULL OR f.MainId IS NOT NULL)
AND
(SELECT e.Value FROM eTable AS e WHERE e.Id = f.SomeId) IS NOT NULL

т.е значения SomeId есть только у корневых записей таблицы Free, а проверить нужно обязательно все записи таблицы Free
т.е если запись корневая, то у нее просто можно взять f.SomeId
а если нет, то нужно получить сначала рутовую запись. Для этого нужен был рекурсивный запрос. Но все равно его применять у меня не получается.

В скалярную функцию оформить тоже почему-то не получается, ругается на with
Да и боюсь накладно выйдет, ведь запросы будут отрабатывать каждую минуту с 20 машин.
7 апр 12, 19:49    [12381151]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Luzerka
Теперь пытаюсь понять, как это использовать для условия.

ничего не понял...может вы выложите исходную таблицу и желаемый результат
7 апр 12, 19:52    [12381161]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Luzerka
Member

Откуда:
Сообщений: 20
Суть вот в чем есть таблицы Task и Emergency
[Приложение]

В таблице задачи могут быть как основные задачи(MainId = null), так и подзадачи(MainId = int value)
Примечание: BaseId относится для другого типа иерархии и в данном случае не имеет значения


Табличная функция, которая вызывается каждую минуту. Данная функция отлично работает для основных задач (MainId = null)
ALTER FUNCTION [dbo].[GetActualTasks]
()
RETURNS 
@table TABLE 
(
	Id int not null,
	EmergencyId int not null,
	UserId int not null,
	Name nvarchar(100) null,
	RemovalDate datetime2(0) not null,
	FactDate datetime null,
	BaseId int null,
	MainId int null
)
AS
BEGIN
	
	DECLARE @UserId AS int
	DECLARE @RoleId AS int
	DECLARE @GroupId AS int
	SELECT @UserId = u.Id, @RoleId = u.RoleId, @GroupId = u.UserGroupId
	FROM dbo.[User] AS u
	WHERE u.[Login] = SYSTEM_USER
	
	INSERT @table
	SELECT t.Id, t.EmergencyId, t.UserId, t.Name, t.RemovalDate, t.FactDate, t.BaseId, t.MainId
	FROM dbo.Task AS t
	JOIN dbo.[User] AS u ON t.UserId = u.Id
	WHERE 
			-- Проверка на то, что задача является не шаблонной
			t.FactDate IS NULL AND 
			(t.EmergencyId IS NOT NULL) AND
			(SELECT e.OriginDate FROM dbo.[Emergency] AS e WHERE e.Id = t.EmergencyId) IS NOT NULL
		AND (
			(@RoleId = 3 AND t.UserId = @UserId) OR
			(@RoleId = 2 AND (t.UserId = @UserId OR u.UserGroupId = @GroupId)) OR
			(@RoleId = 1)
		)
	RETURN
END

В условии выборки, важную роль играет EmergencyId у каждой задачи, но проблема в том, что оно есть только у главных задач(MainId = null) для подзадач оно определяется через главную

Для того, чтобы этот запрос работал, я начал его изменять вот так:

автор
WHERE
-- Проверка на то, что задача является не шаблонной
t.FactDate IS NULL AND
(t.EmergencyId IS NOT NULL OR t.MainId IS NOT NULL) AND
(SELECT e.OriginDate FROM dbo.[Emergency] AS e WHERE e.Id = t.EmergencyId) IS NOT NULL

И в месте помеченым Красным столкнулся с проблемой, что не могу получить EmergecnyId для подзадач.(не могу прикрепить Ваш запрос в условие)

В общем, вот такая беда.
Прошу прощения, что выгрузил фактически всю пробему целиком))
Но раз уж выгрузил, может быть заодно услышу критику о скрипте, потому как думаю, что он далек от какого-то возможного идеала.

К сообщению приложен файл. Размер - 35Kb
7 апр 12, 21:10    [12381397]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Luzerka,

Перепишите функцию примерно так:
ALTER FUNCTION [dbo].[GetActualTasks]
()
RETURNS 
@table TABLE 
(
	Id int not null,
	EmergencyId int not null,
	UserId int not null,
	Name nvarchar(100) null,
	RemovalDate datetime2(0) not null,
	FactDate datetime null,
	BaseId int null,
	MainId int null
)
AS
BEGIN
	
	DECLARE @UserId AS int
	DECLARE @RoleId AS int
	DECLARE @GroupId AS int
	SELECT @UserId = u.Id, @RoleId = u.RoleId, @GroupId = u.UserGroupId
	FROM dbo.[User] AS u
	WHERE u.[Login] = SYSTEM_USER
	
    ;with x as
    (
     select
      Id, EmergencyId, UserId, Name, RemovalDate, FactDate, BaseId, MainId
     from
      dbo.Task
     where
      MainId is null
     
     union all
     
     select
      t.Id, x.EmergencyId, t.UserId, t.Name, t.RemovalDate, t.FactDate, t.BaseId, t.MainId
     from
      x join
      dbo.Task t on t.main_id = x.Id
    ) 
	INSERT @table
	SELECT t.Id, t.EmergencyId, t.UserId, t.Name, t.RemovalDate, t.FactDate, t.BaseId, t.MainId
	FROM x AS t
	JOIN dbo.[User] AS u ON t.UserId = u.Id
	join dbo.[Emergency] e on e.id = t.EmergencyId and e.OriginDate is not null
	WHERE 
			-- Проверка на то, что задача является не шаблонной
			t.FactDate IS NULL AND 
		AND (
			(@RoleId = 3 AND t.UserId = @UserId) OR
			(@RoleId = 2 AND (t.UserId = @UserId OR u.UserGroupId = @GroupId)) OR
			(@RoleId = 1)
		)
	RETURN
END

А лучше сразу поддерживайте актуальное значение EmergencyId на всех уровнях.
7 апр 12, 21:59    [12381550]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Luzerka
Member

Откуда:
Сообщений: 20
Спасибо большое! Помогло!
Но, я так понимаю, это сильно бьет по производительности запроса?
8 апр 12, 19:11    [12383984]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Luzerka
ALTER FUNCTION [dbo].[GetActualTasks] () RETURNS @table TABLE ( ...
А нафига тут table-valued функция, чем inline или даже view не подходит?

FUNCTION [dbo].[Get
Тоже прикалываело. Неужели фунция может Set делать?!
Для удобной сортировки объектов делается именование функций по формуле [тип] + [Объект] + [Прилагательное/действие] (если есть, или не подразумевается). К примеру Get вообще опускается - итак 80% это получение данных.

Не навязываю.
9 апр 12, 10:59    [12386194]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Luzerka
Member

Откуда:
Сообщений: 20
А чем табличная функция хуже представления?))
По поводу правил именования.
Ваша критика наверно очень полезна, но я даже не задумывался о таких вещах. Привык уже, что если что-то получаю - значит Гэт)
12 апр 12, 22:32    [12408731]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Luzerka
А чем табличная функция хуже представления?))
В данном случае табличная функция лишняя. Зачем заставлять сервер делать лишнюю работу?

Необходимый результат можно получить одним запросом
    ;with x as
    (
     select
      Id, EmergencyId, UserId, Name, RemovalDate, FactDate, BaseId, MainId
     from
      dbo.Task
     where
      MainId is null
     
     union all
     
     select
      t.Id, x.EmergencyId, t.UserId, t.Name, t.RemovalDate, t.FactDate, t.BaseId, t.MainId
     from
      x join
      dbo.Task t on t.main_id = x.Id
    ),
    usr as
    (
 	 select
 	  Id, RoleId, UserGroupId
 	 from
 	  dbo.[User]
	 where
	  u.[Login] = system_user
    )
	select
	 t.Id, t.EmergencyId, t.UserId, t.Name, t.RemovalDate, t.FactDate, t.BaseId, t.MainId
	from
	 x as t
	 join dbo.[User] as u on t.UserId = u.Id
	 join dbo.[Emergency] e on e.id = t.EmergencyId and e.OriginDate is not null
	 cross join usr
	where 
	 -- Проверка на то, что задача является не шаблонной
	 t.FactDate is null and 
	 and (
	  (usr.RoleId = 3 and t.UserId = usr.UserId) or
	  (usr.RoleId = 2 and (t.UserId = usr.UserId or u.UserGroupId = usr.GroupId)) or
	  (usr.RoleId = 1)
	 )
И оформить его либо как вью, либо как инлайновую функцию.
12 апр 12, 23:51    [12409081]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Luzerka
А чем табличная функция хуже представления?
Привет процедуромыслящим/императивистам.

Главная разница в том что Table-Valued функция это вызываемый исполняемый объект как процедура и скалярная функция, а представление и параметризованное представление (inline function) - это тупо именованный текст.

Фильтры к табличной функции никак не отразятся на оптимальность исполнения (генерацию плана). Если она "возвращает" 100500 строк. А вам надо Top(1) (или WHERE ID = @ID) сервер всё равно сгенерит эти 100500 строк и затем отсеет эту одну. Про индексы и всё такое можно забыть.

Это не говоря о том что на вызов исполняемого объекта само по себе затратно.

Поэтому table-valued функции используется в очень-очень-очень-очень-очень редких случаях. Когда запросом или одним запросом не написать (к примеру из-за говно-базы написаной процедурномыслящим разрабом).
13 апр 12, 13:11    [12411478]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Luzerka
Member

Откуда:
Сообщений: 20
Огромное спасибо за разъяснение. Теперь вижу насколько я был неправ с выбором функции.
А как насчет хранимых процедур, возвращающих набор данных. Сказаное выше применимо и к ним?
13 апр 12, 13:33    [12411669]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3755
Luzerka
Огромное спасибо за разъяснение. Теперь вижу насколько я был неправ с выбором функции.
А как насчет хранимых процедур, возвращающих набор данных. Сказаное выше применимо и к ним?

к процедурам вообще фильтры не пременишь, они же не in-line
13 апр 12, 13:37    [12411706]     Ответить | Цитировать Сообщить модератору
 Re: Получить корневой элемент  [new]
HeathRow
Member

Откуда:
Сообщений: 22
Luzerka
CREATE TABLE Tree (Id int not null, MainId int null)
1 null
2 1
3 2
4 3

Мне нужно по идентификатору 4, получить рутовый идентификатор 1, в найденых мной примерах выводят все структуры целиком, но я не могу сообразить как это переделать. Помогите пожалуйста.


А если тоже самое но на MSSQL 2000, и например по идентификатору 2 нужно найти саму запись и всю подчиненную ей иерархию, т.е. нужен такой результат:

2 1
3 2
4 3
21 апр 12, 12:09    [12450369]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить