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

Откуда:
Сообщений: 10
Подскажите, пожалуйста, как отсортировать данные по полю empname так, чтобы последовательно сортировались данные только одного уровня иерархии(поле lvl) и иерархия не нарушалась.
Вот таблица:
CREATE TABLE [dbo].[Rubrics](
[empid] [int] NOT NULL,
[hid] [hierarchyid] NOT NULL,
[lvl] AS ([hid].[GetLevel]()) PERSISTED,
[empname] [varchar](25) NOT NULL,
[salary] [money] NOT NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY NONCLUSTERED
(
[empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
------------------------------------------
Вот sql запрос
ELECT empid, REPLICATE(' | ', lvl) + empname AS empname, hid.ToString() AS path
FROM dbo.Rubrics
ORDER BY hid;



==============================================
В результате выходит
empid empname path
1 David /
2 | Eitan /1/
4 | | Seraph /1/1/
5 | | Jiru /1/2/
8 | | | Lilach /1/2/1/
10 | | | Sean /1/2/2/
6 | | Steve /1/3/
3 | Ina /2/
7 | | Aaron /2/1/
9 | | | Rita /2/1/1/
12 | | | | Emilia /2/1/1/1/
13 | | | | Michael /2/1/1/2/
14 | | | | Didi /2/1/1/3/
11 | | | Gabriel /2/1/2/

===================================================
А НАДО
empid empname path
1 David /
2 | Eitan /1/
5 | | Jiru /1/2/
8 | | | Lilach /1/2/1/
10 | | | Sean /1/2/2/
4 | | Seraph /1/1/
6 | | Steve /1/3/
3 | Ina /2/
7 | | Aaron /2/1/
9 | | | Rita /2/1/1/
12 | | | | Emilia /2/1/1/1/
14 | | | | Didi /2/1/1/3/
13 | | | | Michael /2/1/1/2/
11 | | | Gabriel /2/1/2/




---------------------------------------
ТО есть получается, что на все сортируется по иерархии, а хочется чтобы каждый уровень иерархии сортировался еще и по empname.
Я уже думал сделать все это с помощью рекурсии, но , может, есть более простой вариант?
26 сен 09, 13:48    [7711332]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
ORDER BY hid, empname ?
26 сен 09, 19:52    [7711842]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Epic fail.

Max21951
Я уже думал сделать все это с помощью рекурсии, но , может, есть более простой вариант?
Думаю прикидываться ГариПотером глупо, всё таки надо понимать физику, чтоб называться специалистом, а не домохозяйкой.

1. Зачем вам сортировка на сервере? Аля красивые рюшечки это проблемы клиента, а не сервера.
2. Зачем вы вааще используете HIERARCHYID? Как раз на стадии вставки данных и нужно было это делать, в нужную точку пути (согласно сортировке), а не просто в конец. Ну и вытекающие последствия скорости и размера.
3. Вынужденная неэффективная рекурсия, так как лень переделать?

Гавриленко Сергей Алексеевич
ORDER BY hid, empname ?
С каких пор сервер читает мысли?
26 сен 09, 20:01    [7711856]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Max21951
Member

Откуда:
Сообщений: 10
Я это делаю, потому что нужно сделать иерархическое меню для сайта(рубрики). И пользователям будет неудобно искать рубрику, если она будет не по алфавиту. Тогда еще 1 вопрос. Я не совсем представляю, как на одном уровне иерархии можно вставлять данные в нужную позицию согласно алфавитув(полне возможно, что потом данные будут добавляться).
27 сен 09, 08:20    [7712386]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
varavva_kp
Member

Откуда: Харьков, Киев, ...
Сообщений: 4
Max21951,

Поделюсь своим методом решения:
в результате получаю примерно такое (иерархический список где все элементы каждого уровня отсортированы по алфавиту)
# Объект
1 » Документы
2 »» Другие
3 »»» Доверенность выданная
4 »»» Доверенность полученная
5 »»» Налоговые накладные
6 »»» Паспорт
7 »» Расчетно-кассовые документы
8 »»» Авансовый отчет (оплата контрагенту)
9 »»» Авансовый отчет (по расходам)
10 »»» Поступление на расчетный счет
11 »»» Приходный кассовый ордер
12 »»» Расходный кассовый ордер
13 »»» Списание с расчетного счета
14 »» Товарные документы
15 »»» Акт выполненных работ полученный
16 »»» Возвратная накладная от покупателя
17 »»» Возвратная накладная поставщику
18 »»»Приходная накладная
19 »»» Расходная накладная
20 »»» Склад поставщика
21 »»» Счет-фактура
22 » Документы пользователей


Исходные данные:
таблица SQL
[object_id] [smallint] IDENTITY(1,1) NOT NULL,
[object_cat] [smallint] NOT NULL,
[object_name] [varchar](50) NOT NULL,
где object_cat - номер элемента родителя (начальника) или 0 если этот элемент корневой

Я приведу рекурсивную функцию на PHP (под другой язык можно легко переписать)

function get_objects_list()
{
global $db;
global $obj_list;
$obj_list = array();

function ShowTree($ParentID, $lvl)
{
global $lvl;
global $db;
global $obj_list;
$lvl++;
$s_sql = "SELECT * FROM " . OBJECTS_TABLE . " WHERE object_cat = " . $ParentID . " ORDER BY object_name";
if( !($result = $db->sql_query($s_sql)) )
{
message_die(GENERAL_ERROR, 'Could not query', '', __LINE__, __FILE__, $s_sql);
}
$result = $db->sql_query($s_sql);
if (($db->sql_numrows($result)) > 0)
{
while ( $row = $db->sql_fetchrow($result) )
{
$ID1 = $row['object_id'];
$obj_list[] = array('object_id' => $row['object_id'], 'object_name' => $row['object_name'], 'object_cat' => $row['object_cat'], 'object_level' => ($lvl - 1));
ShowTree($ID1, $lvl);
$lvl--;
}
}
}


ShowTree(0, 0);
return $obj_list;
}


Думаю что данный вариант не самый шустрый по исполнению, но я использую данный шаблон несколько лет.

Если есть более красивые решения (быстрее исполняются) - возьму на вооружение.
27 сен 09, 14:47    [7712709]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Max21951
Member

Откуда:
Сообщений: 10
varavva_kp,
спасибо за совет. Наверно я так и реализую, но на asp.net.
Просто я думал, что HIERARCHYID даст более красивое и быстрое решение
27 сен 09, 16:45    [7712815]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Max21951
Я не совсем представляю, как на одном уровне иерархии можно вставлять данные в нужную позицию согласно алфавиту
А как вы вааще вставляете данные в HIERARCHYID?
Нужно при получении очередного hid-а через <Parent>.GetDescendant(<ChildPrevious>,<ChildNext>) подставить правильные значения. Что-то типа:
USE TempDB
GO
CREATE TABLE [dbo].[Employee] (
	 [ID]		SmallInt	IDENTITY
	 CONSTRAINT [PK_Employee]	PRIMARY KEY
	,[Parent]	SmallInt
	 CONSTRAINT [FK_Employee]	FOREIGN KEY [dbo].[Employee] ([ID])
	,[HID]		HierarchyID	NOT NULL
	 CONSTRAINT [UK_Employee]	UNIQUE
	,[Name]		VarChar(250)	NOT NULL
	,[Salary]	Money		NOT NULL
)
GO
CREATE INDEX [IX_Employee_Parent] ON [dbo].[Employee] ([Parent],[Name])
GO
CREATE TRIGGER [Employee_Insert] ON [dbo].[Employee]
INSTEAD OF INSERT AS BEGIN
	INSERT	dbo.Employee (
		 Parent
		,HID
		,Name
		,Salary
	)SELECT	 I.Parent
		,IsNull(P.HID.GetDescendant(L.HID,R.HID),HierarchyID::GetRoot())
		,I.Name
		,I.Salary
	FROM	          Inserted		I
		LEFT JOIN dbo.Employee	P ON P.ID = I.Parent
		OUTER APPLY(
			SELECT	Top(1) *
			FROM	dbo.Employee	L
			WHERE	    L.Parent	 = P.ID
				AND L.Name	<= I.Name
			ORDER BY L.Name DESC
				,L.HID DESC)	L
		OUTER APPLY(
			SELECT	Top(1) *
			FROM	dbo.Employee	R
			WHERE	    R.Parent	 = P.ID
				AND R.Name	>  I.Name
			ORDER BY R.Name
				,R.HID)		R
END
GO
CREATE TRIGGER [Employee_Update] ON [dbo].[Employee]
INSTEAD OF UPDATE AS BEGIN
	IF Update(Parent)
	OR Update(Name) BEGIN
		-- Переписать HID-ы изменённых строк и их потомков :)
		-- На ночь глядя думать не хочется
		-- Задача в адын запрос интересна, но не более

		UPDATE	T
		SET	 Parent	= I.Parent
			,Name	= I.Name
			,Salary	= I.Salary
		FROM	     Inserted		I
			JOIN dbo.Employee	T ON T.ID = I.ID
	END ELSE
		UPDATE	T
		SET	Salary	= I.Salary
		FROM	     Inserted		I
			JOIN dbo.Employee	T ON T.ID = I.ID
END
GO
На заметку: Методы HIERARCHYID

И главный вопрос, зачем вам HIERARCHYID вааще сдался? Где вы им пользуетесь?
28 сен 09, 03:35    [7713554]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Max21951
Member

Откуда:
Сообщений: 10
Mnior,
я собирался использовать HIERARCHYID, чтобы рубрикатор на сайте был неограниченной степени вложенности.
28 сен 09, 06:23    [7713589]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Max21951,

HIERARCHYID не обеспечит вам неограниченную степень вложенности, он всё же имеет свои пределы. И HIERARCHYID нужен для сложных выборок, например если у вас есть иерархия категорий товаров, то вы легко и быстро сможете получить все вложенные подкатегории, используя HIERARCHYID. Вы делаете, что-то подобное в вашем проекте? Или же вы хотели использовать его только для сортировки выборки?
28 сен 09, 10:56    [7714236]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Max21951
Member

Откуда:
Сообщений: 10
Denis Reznik,

Да. мне нужно будет получать товары из рубрики и из всех ее дочерних рубрик.
28 сен 09, 18:59    [7717167]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Max21951,

ну, тогда используйте :), но сортировать выборку вам всё равно придётся либо на кленте, либо на сервере, либо использовать способ вставки, предложенный выше, т.к. HierarchyId не умеет того, что вам надо. И если у вас рубрикатор меняется относительно редко, то большой беды, в сортировке я не вижу, т.к его можно кэшировать на довольно длительное время.
28 сен 09, 19:31    [7717249]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Max21951
Mnior,
я собирался использовать HIERARCHYID, чтобы рубрикатор на сайте был неограниченной степени вложенности.
"Зачем мне холодильник, если я не курю."
Что значит "ограниченная вложенность", что вааще обладает этим свойством? Где в теории деревьев оговаривается это понятие?

В теории представления деревьев в реляционных базах описываются несколько (основных кажись три или четыре) способов. И нигде не помню описания об "ограниченной вложенности". Каждый способ обладает набором преимуществ и недостатков.

KO:
В типе HIERARCHYID хранится (компактно) полный путь от корня, в классическом методе представления только ссылка на родителя. Для HIERARCHYID изменение данных это огромнейший геморрой, зато некоторые запросы могут быть просты и быстры, в классическом - изменение данных проще не бывает, но некоторые запросы неэффективны.

Max21951
Да. мне нужно будет получать товары из рубрики и из всех ее дочерних рубрик.
Т.е. вы пользуетесь свойствами представления дерева через хранения пути. Хорошо.
Только чёта я не вижу что вы понимаете какой метод эффективней в вашем случае, т.к. из первого вашего поста ясно, что вы свободно можете позволить себе рекурсию при выборке. Блин, возможно рекурсивно и из классического представления деревьев собрать чтовамнада, без HIERARCHYID.

Denis Reznik
HierarchyId не умеет того, что вам надо
Уточню: умеет, просто им неправильно пользуются. Не добавить всего лишь один дополнительный OUTER APPLY для GetDescendant глупо, а изменение положения веток в дереве также геморно, хоть с сортировкой по Name, хоть без - это просто свойство подхода HIERARCHYID.
28 сен 09, 21:48    [7717520]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Max21951
Member

Откуда:
Сообщений: 10
Спасибо всем за ответы. Буду делать таблицу тегов с использованием HIERARCHYID и триггера для вставки
30 сен 09, 16:30    [7725766]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Max21951
Member

Откуда:
Сообщений: 10
Mnior, спасибо за твой триггер для вставки данных. Не можешь ли ты подсказать, что неправильно в моем триггере:
USE [photodoska]
GO
/****** Object: Trigger [dbo].[RubricDelete] Script Date: 10/01/2009 22:46:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[RubricDelete]
ON [dbo].[Rubrics]
FOR DELETE
AS
BEGIN
DELETE FROM Rubrics WHERE ID IN (SELECT C.ID
FROM dbo.Rubrics AS P
JOIN dbo.Rubrics AS C
ON P.id IN (SELECT id from deleted)
AND C.hid.IsDescendantOf(P.hid) = 1)
END
.
При всем этом запрос
SELECT C.ID
FROM dbo.Rubrics AS P
JOIN dbo.Rubrics AS C
ON P.id =@id
AND C.hid.IsDescendantOf(P.hid) = 1
Выбирает детей удаляемого элемента, но при работе триггера дети почему-то не удаляются.
1 окт 09, 20:27    [7731852]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Max21951,

замените FOR на INSTEAD OF, тогда всё удалится. Но всё же, если вы не удаляете по нескольку записей за раз, то рекомендую удалять храникой вместо использования триггера. Можно использовать что-то вроде:

CREATE PROCEDURE dbo.DeleteSomeData
	@Id int
AS
	DECLARE @node hierarchyId;
	SELECT @node = hid FROM dbo.Rubrics WHERE id = @Id;
	DELETE FROM dbo.Rubrics WHERE hid.IsDescendantOf(@node) = 1;
GO
2 окт 09, 02:04    [7732327]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Зачем процедуру и INSTEAD OF?
Просто человек не понял про FOR - это триггер AFTER, поэтому лучше не писать FOR для понимания.
ALTER TRIGGER [dbo].[RubricDelete] ON [dbo].[Rubrics]
AFTER DELETE AS BEGIN
	DELETE	С
	FROM	     Deleted		D
		JOIN dbo.Rubrics	C ON C.HID.IsDescendantOf(D.HID) = 1
END
GO
Удивлён, что IsDescendantOf прямо участвует в индексе. Молодцы мелкомягкие.
2 окт 09, 10:19    [7733018]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
Удивлён, что IsDescendantOf прямо участвует в индексе.
Хотя это смахивает на грязный хак.
2 окт 09, 10:35    [7733163]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Mnior
Mnior
Удивлён, что IsDescendantOf прямо участвует в индексе.
Хотя это смахивает на грязный хак.

да выглядит как-то неестественно :)

А процедура, на мой взгляд, всё равно предпочтительней, потому как выполняться будет быстрее (в том числе и при инсёрте и апдейте). Но подозреваю, что это тот случай, когда перфоманс не сильно важен, так что на усмотрение автора остаётся что использовать.
2 окт 09, 11:50    [7733848]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Denis Reznik
А процедура, на мой взгляд, всё равно предпочтительней, потому как выполняться будет быстрее
1. Большая надёжность и гибкость важнее малого перформанса (в основном). Вот когда возникают невыносимые тормоза, а надёжность отходит на второй план, тогда программер с кислой мордой прощается с гибкостью.
2. Из-за гибкости ваше утверждение спорно. Если в пяти функционалах надо модифицировать эту таблу, то вам надо либо повторить все проверки и логики - убийство, или вызывать общую процедуру - в курсоре для каждой записи? - извращение и верх тормознутости.

Локальная скорость это нуль без палочки относительно глобальной. Локальные скорости не складываются, потому что их требования очень часто противоречивы.
2 окт 09, 22:24    [7737359]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Mnior
Если в пяти функционалах надо модифицировать эту таблу

в большинстве случаев достаточно всего одной хранимки для операции

Mnior
или вызывать общую процедуру - в курсоре для каждой записи? - извращение и верх тормознутости

по моему курсоры это уже верх тормознутости

Нет, я не считаю, что триггеры не нужно использовать, но всё же думаю нужно использовать аккуратно и только при необходимости
3 окт 09, 01:05    [7737575]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Aitov
Member

Откуда:
Сообщений: 13
Хороший пример для сортировки при вставке... А вот мне надо сортировать потомков для каждого узла не только по алфавиту, но и с начала по типу объекта.. То есть типы показываются группами и потом уже каждый тип по алфавиту.... Как мне получить при ставке объекта getdescendant(child1, child2) то есть предыдущий и следующий объекты. Голову сломал, но хочу делать сортировку на сервере а не на клиенте(
13 авг 11, 23:09    [11115295]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
Aitov,

для начала рекомендую оформить свой вопрос соответственно.
14 авг 11, 14:09    [11116305]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Aitov
Member

Откуда:
Сообщений: 13
Виноват, имелся в виду пример в сообщении [7713554] , в этой же теме, выше... Все же прошу помощи)
15 авг 11, 10:33    [11118320]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
Aitov
имелся в виду пример в сообщении [7713554]


Это отлично, но выполняет ли пример указания п.6 Правил по моей ссылке?
Обратите внимание на линк Вот пример хорошего стиля в том же 6-м пункте.
Обход иерархии - тема всегда не из простых, что бы получить помощь нужно зело постараться...
15 авг 11, 13:47    [11119859]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка иерархических данных HIERARCHYID  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
Aitov
Хороший пример для сортировки при вставке... А вот мне надо сортировать потомков для каждого узла не только по алфавиту, но и с начала по типу объекта.. То есть типы показываются группами и потом уже каждый тип по алфавиту.... Как мне получить при ставке объекта getdescendant(child1, child2) то есть предыдущий и следующий объекты. Голову сломал, но хочу делать сортировку на сервере а не на клиенте(
Приведи свой пример... И пример того что хочешь получить... Но чтобы он наглядный был, используй скрипты оформительские...
Для примера:
declare @t_tree table (id int not null, id_par int, name varchar(1000) not null, ord int not null)
insert into @t_tree (id,id_par,name,ord) Values 
(1, null, 'Узел 1', 1),
(2, null, 'Узел 2', 2),
(6,   2, 'Узел 21', 6),
(7,   6, 'Узел 211', 7),
(3,   1, 'Узел 11', 3),
(4,   3, 'Узел 111', 4),
(5,   1, 'Узел 12', 5)


SELECT * FROM @t_tree
15 авг 11, 15:09    [11120607]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить