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

Откуда:
Сообщений: 216
Всем привет

Есть справочник, несбалансированный древовидный.
автор
ROOT_LEVEL_ID ARTICLE_ID ARTICLE_NAME LEVEL IS_LEAF
1 1. Основной уровень 1 0 0
1 1.1 Статья уровня 1.1 1 1
1 1.2 Статья уровня 1.2 1 0
1 1.2.1 Статья уровня 1.2.1 2 1
1 1.2.2 Статья уровня 1.2.2 2 1
1 1.2.3 Статья уровня 1.2.3 2 1
1 1.3 Статья уровня 1.3 1 1
2 2. Основной уровень 2 0 0
2 2.1 Статья уровня 2.1 1 1

IF OBJECT_ID('tempdb..#tArticles') IS NOT NULL
	DROP TABLE #tArticles;
IF OBJECT_ID('tempdb..#tTrans') IS NOT NULL
	DROP TABLE #tTrans;

CREATE TABLE #tArticles(
	[ROOT_LEVEL_ID] [nvarchar](20) NULL,
	[ARTICLE_ID] [nvarchar](20) NULL,
	[ARTICLE_NAME] [nvarchar](100) NULL,
	[LEVEL] [int] NULL,
	[IS_LEAF] [int] NULL
) ON [PRIMARY];

CREATE TABLE #tTrans(
	[ARTICLE_ID] [nvarchar](20) NULL,
	[AMOUNT] float
) ON [PRIMARY];


INSERT INTO #tArticles (ROOT_LEVEL_ID, [ARTICLE_ID], [ARTICLE_NAME], [LEVEL], [IS_LEAF])
VALUES ('1', '1.', 'Основной уровень 1', 0, 0),
	 ('1', '1.1', 'Статья уровня 1.1', 1, 1),
	 ('1', '1.2', 'Статья уровня 1.2', 1, 0),
	 ('1', '1.2.1', 'Статья уровня 1.2.1', 2, 1),
	 ('1', '1.2.2', 'Статья уровня 1.2.2', 2, 1),
	 ('1', '1.2.3', 'Статья уровня 1.2.3', 2, 1),
	 ('1', '1.3', 'Статья уровня 1.3', 1, 1),
	 ('2', '2.', 'Основной уровень 2', 0, 0),
	 ('2', '2.1', 'Статья уровня 2.1', 1, 1);

 INSERT INTO #tTrans (ARTICLE_ID, AMOUNT)
 VALUES ('1.2.1', 12),
	('1.2.2', 3),
	('1.2.3', 5),
	('1.2.3', 10),
	('2.1', 100),
	('1.1', 55);


SELECT * FROM #tArticles ORDER BY ARTICLE_ID;
SELECT * FROM #tTrans;


Необходимо по этому справочнику собрать факт снизу вверх таким образом, чтобы с листьев факт собирался по транзакциям, а на уровни выше - уже по собранным статьям
Типа:
Статья уровня 1.1 = 55
Статья уровня 1.2.1 сумма 12
Статья уровня 1.2.2 сумма 3
Статья уровня 1.2.3 сумма 5+10 = 15
Статья уровня 1.2 сумма 12 + 3 + 15 = 30
Статья уровня 1 сумма 55 + 30 = 85

Не могу придумать запрос, подскажите направление, пожалуйста :)

Спасибо!
17 мар 17, 16:47    [20306806]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Новичок_я
Member

Откуда:
Сообщений: 216
У меня из идей только CTE + View
17 мар 17, 16:49    [20306816]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 4464
Новичок_я,

Вы какую-то свою терминология сочиняете. Транзакция и статья в сиквеле имеет совсем другой смысл.
17 мар 17, 17:14    [20306927]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 2371
писать уже лень :) вам нужно просто считать сумму по всей ветке статей в проводках, получаете всех детей для каждой статьи и считаете сумму.

автор
чтобы с листьев факт собирался по транзакциям, а на уровни выше - уже по собранным статьям

при таком раскладе надо хранить итоги
17 мар 17, 17:22    [20306961]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Новичок_я
Member

Откуда:
Сообщений: 216
Владислав Колосов
Новичок_я,

Вы какую-то свою терминология сочиняете. Транзакция и статья в сиквеле имеет совсем другой смысл.


Ок, это не терминология сиквела.
Если ввел этим в заблуждение, прошу прощения.
Но сути вопроса, кажется, это не меняет. Да и пример кода и описание все же поясняют, что под транзакцией не имеется в виду транзакция сервера
17 мар 17, 17:33    [20307016]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
o-o
Guest
Владислав Колосов
Новичок_я,

Вы какую-то свою терминология сочиняете. Транзакция и статья в сиквеле имеет совсем другой смысл.

а при чем тут, как и что у него называется.
может, это по карточкам транзакции.
почему юзерским таблицам нельзя описывать юзерские же (банковские?) сущности?
17 мар 17, 17:33    [20307017]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Новичок_я
Member

Откуда:
Сообщений: 216
TaPaK
писать уже лень :) вам нужно просто считать сумму по всей ветке статей в проводках, получаете всех детей для каждой статьи и считаете сумму.

автор
чтобы с листьев факт собирался по транзакциям, а на уровни выше - уже по собранным статьям

при таком раскладе надо хранить итоги


Хранить итоги не очень хочется, накладно это.

Вы имеете в виду, что для каждой статьи, даже имеющей потомков, надо считать через статьи самых нижних уровней?
ТОже дороговато, хотя и менее, чем хранить итоги :)
17 мар 17, 17:35    [20307027]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 2371
TaPaK
писать уже лень :) вам нужно просто считать сумму по всей ветке статей в проводках, получаете всех детей для каждой статьи и считаете сумму.

автор
чтобы с листьев факт собирался по транзакциям, а на уровни выше - уже по собранным статьям

при таком раскладе надо хранить итоги

мы в общем храним итоги только для листьев, но суть у нас "чуть" больше, а узлы и все остальные уровни считаются как писал
17 мар 17, 17:37    [20307030]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 2371
Новичок_я
TaPaK
писать уже лень :) вам нужно просто считать сумму по всей ветке статей в проводках, получаете всех детей для каждой статьи и считаете сумму.

пропущено...

при таком раскладе надо хранить итоги


Хранить итоги не очень хочется, накладно это.

Вы имеете в виду, что для каждой статьи, даже имеющей потомков, надо считать через статьи самых нижних уровней?
ТОже дороговато, хотя и менее, чем хранить итоги :)

если у вас всё так просто как вы описываете, то cte вам всё порешает
17 мар 17, 17:41    [20307043]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
invm
Member

Откуда: Москва
Сообщений: 7122
with a as
(
 select
  *, hierarchyid::Parse(replace('/' + replace(article_id, '.', '/') + '/', '//', '/')) as h
 from
  #tArticles
),
b as
(
 select
  a.ARTICLE_ID, a.ARTICLE_NAME, a.h, isnull(t.AMOUNT, 0) as AMOUNT
 from
  a left join
  #tTrans t on t.ARTICLE_ID = a.ARTICLE_ID
 where
  IS_LEAF = 1

 union all

 select
  a.ARTICLE_ID, a.ARTICLE_NAME, a.h, b.AMOUNT
 from
  b join
  a on a.h = b.h.GetAncestor(1)
)
select
 b.ARTICLE_ID, b.ARTICLE_NAME, sum(b.AMOUNT)
from
 b
group by
 b.ARTICLE_ID, b.ARTICLE_NAME, b.h
order by
 b.h;
17 мар 17, 17:58    [20307125]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Новичок_я
Member

Откуда:
Сообщений: 216
Гм, занятно, спасибо
17 мар 17, 18:02    [20307142]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Wlr-l
Member

Откуда:
Сообщений: 427
Очевидно, что этот справочник несбалансированный и древовидный меняется не часто, скорее всего один раз в год. Поэтому можно использовать множественную модель деревьев Джо Селко. При построении дерева достаточно один раз обойти его слева направо и результат обхода записать в lft и rgt. В этом случае запросы к дереву будут очень простыми. Например:

if object_id('tempdb..#tarticles') is not null drop table #tarticles;
if object_id('tempdb..#ttrans')    is not null drop table #ttrans;

create table #tarticles (
 article_id nvarchar(20)  null,
 level      int           null,
 is_leaf    int           null,
 lft        int           null,
 rgt        int           null,
);

create table #ttrans (
 article_id nvarchar(20) null,
 amount     float        null
);


insert #tarticles (article_id, level, is_leaf, lft, rgt)
  values          (       '0',     0,       0,   1,  20),
                  (       '1.',    0,       0,   2,  15),
                  (      '1.1',    1,       1,   3,   4),
                  (      '1.2',    1,       0,   5,  12),
                  (    '1.2.1',    2,       1,   6,   7),
                  (    '1.2.2',    2,       1,   8,   9),
                  (    '1.2.3',    2,       1,  10,  11),
                  (      '1.3',    1,       1,  13,  14),
                  (       '2.',    0,       0,  16,  19),
                  (      '2.1',    1,       1,  17,  18);

 insert #ttrans (article_id, amount)
   values          ('1.2.1',     12),
                   ('1.2.2',      3),
                   ('1.2.3',      5),
                   ('1.2.3',     10),
                   (  '2.1',    100),
                   (  '1.1',    55);


--select * from #tarticles order by article_id;
--select * from #ttrans;

with a as (
  select a.article_id, amount, lft, rgt
    from      #tarticles a
    left join #ttrans    b on b.article_id=a.article_id
)

select a.article_id, sum(b.amount)
  from a a
  join a b on b.lft between a.lft and a.rgt
  where a.article_id<>'0'
  group by a.article_id;

Результат:
1.	85
1.1 55
1.2 30
1.2.1 12
1.2.2 3
1.2.3 30
1.3 NULL
2. 100
2.1 100
20 мар 17, 16:07    [20314508]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Ролг Хупин
Member

Откуда: Литва
Сообщений: 813
Wlr-l
Очевидно, что этот справочник несбалансированный и древовидный меняется не часто, скорее всего один раз в год. Поэтому можно использовать множественную модель деревьев Джо Селко. При построении дерева достаточно один раз обойти его слева направо и результат обхода записать в lft и rgt. В этом случае запросы к дереву будут очень простыми.



Всё вроде бы правильно, но есть примечания:

Целко писал о таком алгоритме уже лет 15 назад
На больших объемах работает не очень хорошо
С тех пор появились другие решения, например, hierarchyid
20 мар 17, 16:28    [20314690]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Wlr-l
Member

Откуда:
Сообщений: 427
Ролг Хупин,

Голословные утверждения. Запросы в деревьях Селко работают быстрее других решений на любых объемах.
Ограничение не в объеме дерева, а в частых вставках в дерево, когда приходится пересчитывать границы.

Процессы в атомах происходили и происходят с момента возникновения Вселенной, но люди совсем недавно научились их использовать.

С тех пор появились другие решения, например, hierarchyid

Я не против, используйте!
20 мар 17, 16:40    [20314760]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Ролг Хупин
Member

Откуда: Литва
Сообщений: 813
Wlr-l
Ролг Хупин,

Голословные утверждения. Запросы в деревьях Селко работают быстрее других решений на любых объемах.
Ограничение не в объеме дерева, а в частых вставках в дерево, когда приходится пересчитывать границы.

Процессы в атомах происходили и происходят с момента возникновения Вселенной, но люди совсем недавно научились их использовать.

С тех пор появились другие решения, например, hierarchyid

Я не против, используйте!


я использую, отличный тип данных, быстро и надежно.
20 мар 17, 17:07    [20314890]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Wlr-l
Member

Откуда:
Сообщений: 427
Ролг Хупин,

Осталось дождаться Вашего решения этой задачи.
20 мар 17, 17:12    [20314909]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 183
Wlr-l
insert #tarticles (article_id, level, is_leaf, lft, rgt)

А алгоритм формирования lft и rgt в общем виде есть? или вы его нарисовали руками, а потом в инсерт?
20 мар 17, 17:17    [20314929]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Ролг Хупин
Member

Откуда: Литва
Сообщений: 813
Wlr-l
Ролг Хупин,

Осталось дождаться Вашего решения этой задачи.



Такая задача не решается, вы не влезете в транзакцию, вас там не ждут

"Необходимо по этому справочнику собрать факт снизу вверх таким образом, чтобы с листьев факт собирался по транзакциям, а на уровни выше - уже по собранным статьям"
20 мар 17, 17:24    [20314953]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Wlr-l
Member

Откуда:
Сообщений: 427
Руслан Дамирович,

Есть, описано в книгах и статьях Джо Селко. У нас это реализовано хранимой процедурой.

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

Аналогичное преобразование сделано и для hierarchyid.
20 мар 17, 17:31    [20314984]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Wlr-l
Member

Откуда:
Сообщений: 427
Ролг Хупин
Wlr-l
Ролг Хупин,

Осталось дождаться Вашего решения этой задачи.



Такая задача не решается, вы не влезете в транзакцию, вас там не ждут

"Необходимо по этому справочнику собрать факт снизу вверх таким образом, чтобы с листьев факт собирался по транзакциям, а на уровни выше - уже по собранным статьям"


Есть категория людей, которые могут умничать, а как только попросишь привести свое решение - сразу в кусты.
20 мар 17, 17:33    [20314991]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 183
Wlr-l
Руслан Дамирович,
Есть, описано в книгах и статьях Джо Селко. У нас это реализовано хранимой процедурой.
В данном, очень простом, случае я вручную обошел дерево прямо в тексте запроса.
Аналогичное преобразование сделано и для hierarchyid.

"... но игнорирую суть вопроса"
Я попросил конкретный SQL-запрос, как сформировать предложенный вами NESTED SET, ибо без этого - ваше решение неполное.
20 мар 17, 18:14    [20315123]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Wlr-l
Member

Откуда:
Сообщений: 427
Руслан Дамирович,

Учебный пример преобразования таблицы смежности в множественную модель (предложенную, ужас, в прошлом веке):


-- Изначально стек пустой, он будет содержать модель вложенных множеств
if object_id(N'Stack') is not null drop table Stack;
CREATE TABLE Stack (
 stack_top INTEGER  NOT NULL,
 emp       CHAR(10) NOT NULL,
 lft       INTEGER      NULL,
 rgt       INTEGER      NULL
);

DECLARE @counter     INTEGER;
DECLARE @max_counter INTEGER;
DECLARE @current_top INTEGER;

SET @counter    =2;
SET @max_counter=2*(SELECT COUNT(*) FROM Tree);
SET @current_top=1;

INSERT INTO Stack SELECT 1,emp,1, @max_counter FROM Tree WHERE boss IS NULL;
DELETE FROM Tree WHERE boss IS NULL;

WHILE @counter<=(@max_counter-1) BEGIN
 IF EXISTS (SELECT * FROM Stack AS S1, Tree AS T1
             WHERE S1.emp=T1.boss AND S1.stack_top=@current_top)
    BEGIN --вершина имеет подчиненные вершины, заносим новое значение lft в стек
      INSERT INTO Stack
         SELECT (@current_top+1), MIN(T1.emp), @counter, NULL
         FROM Stack S1, Tree T1
         WHERE S1.emp=T1.boss AND S1.stack_top=@current_top;
      DELETE FROM Tree
         WHERE emp=(SELECT emp FROM Stack WHERE stack_top=@current_top+1);
      SET @counter=@counter+1;
      SET @current_top=@current_top+1;
    END
 ELSE BEGIN --выталкиваем значение из стека и определяем значение rgt
        UPDATE Stack
          SET rgt=@counter,
              stack_top=-stack_top -- pops the stack
          WHERE stack_top=@current_top
        SET @counter=@counter+1;
        SET @current_top=@current_top-1;
      END;
END;

select * from personnel;
select * from Stack;
20 мар 17, 18:22    [20315153]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Ролг Хупин
Member

Откуда: Литва
Сообщений: 813
Wlr-l
Ролг Хупин
пропущено...



Такая задача не решается, вы не влезете в транзакцию, вас там не ждут

"Необходимо по этому справочнику собрать факт снизу вверх таким образом, чтобы с листьев факт собирался по транзакциям, а на уровни выше - уже по собранным статьям"


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


это еще ладно, а есть еще юзеры, которые задают вопрос и ждут решения

"Осталось дождаться Вашего решения этой задачи."
20 мар 17, 18:27    [20315162]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Wlr-l
Member

Откуда:
Сообщений: 427
1.Диалог:
- А алгоритм формирования lft и rgt в общем виде есть?
- Есть, описано в книгах и статьях Джо Селко.
- Я попросил конкретный SQL-запрос!

т.е. алгоритм = SQL-конкретный запрос!
Вообще-то алгоритмы обхода дерева описаны в учебниках по дискретной математике или программированию, если в них рассматриваются деревья. Джо Селко немного модифицировал один из них, предложив для каждой вершины два числа lft, rgt.

2.Я не предлагал NESTED SET, это сделал Джо Селко в прошлом веке. Я лишь показал, как решить эту задачу с помощью NESTED SET.

3.Решение с hierarchyid было дано ранее invm. Если включить столбец с hierarchyid в таблицу, то не нужно будет каждый раз его формировать, т.е. запрос а будет лишним.

4.hierarchyid не так прост, попробуйте переместить поддерево. Но это свойственно всем моделям представления деревьев реляционными таблицами. Кроме того рекурсия осталась. Hierarchyid, мне так кажется, произошел от "деревьев, не требующих поддержки", Итцика Бен-Гана.

5.Я вчера спешил и привел неполный пример. Приведу полный пример из статьи Джо Селко, немного модифицированный под T-SQL. Может быть синтаксис и устарел (это было давно), но пример можно скопировать, выполнить и посмотреть результат:
+
if object_id(N'personnel') is not null drop table personnel;
CREATE TABLE personnel (
 emp    CHAR(20)     PRIMARY KEY,
 boss   CHAR(20)     NULL REFERENCES Personnel(emp), 
 salary DECIMAL(6,2) NOT NULL
)
insert into personnel (emp,boss,salary) values('Jerry', NULL,   1000.00)
insert into personnel (emp,boss,salary) values('Bert',  'Jerry', 900.00)
insert into personnel (emp,boss,salary) values('Chuck', 'Jerry', 900.00)
insert into personnel (emp,boss,salary) values('Donna', 'Chuck', 800.00)
insert into personnel (emp,boss,salary) values('Eddie', 'Chuck', 700.00)
insert into personnel (emp,boss,salary) values('Fred',  'Chuck', 600.00)
select * from personnel;

if object_id(N'Tree') is not null drop table Tree;
CREATE TABLE Tree (
 emp  CHAR(10) NOT NULL,
 boss CHAR(10)     NULL
);

INSERT INTO Tree SELECT emp,boss FROM personnel;
select * from Tree;

-- Изначально стек пустой, он будет содержать модель вложенных множеств
if object_id(N'Stack') is not null drop table Stack;
CREATE TABLE Stack (
 stack_top INTEGER  NOT NULL,
 emp       CHAR(10) NOT NULL,
 lft       INTEGER      NULL,
 rgt       INTEGER      NULL
);

DECLARE @counter     INTEGER;
DECLARE @max_counter INTEGER;
DECLARE @current_top INTEGER;

SET @counter    =2;
SET @max_counter=2*(SELECT COUNT(*) FROM Tree);
SET @current_top=1;

INSERT INTO Stack SELECT 1,emp,1, @max_counter FROM Tree WHERE boss IS NULL;
DELETE FROM Tree WHERE boss IS NULL;

WHILE @counter<=(@max_counter-1) BEGIN
 IF EXISTS (SELECT * FROM Stack AS S1, Tree AS T1
             WHERE S1.emp=T1.boss AND S1.stack_top=@current_top)
    BEGIN --вершина имеет подчиненные вершины, заносим новое значение lft в стек
      INSERT INTO Stack
         SELECT (@current_top+1), MIN(T1.emp), @counter, NULL
         FROM Stack S1, Tree T1
         WHERE S1.emp=T1.boss AND S1.stack_top=@current_top;
      DELETE FROM Tree
         WHERE emp=(SELECT emp FROM Stack WHERE stack_top=@current_top+1);
      SET @counter=@counter+1;
      SET @current_top=@current_top+1;
    END
 ELSE BEGIN --выталкиваем значение из стека и определяем значение rgt
        UPDATE Stack
          SET rgt=@counter,
              stack_top=-stack_top -- pops the stack
          WHERE stack_top=@current_top
        SET @counter=@counter+1;
        SET @current_top=@current_top-1;
      END;
END;

select * from Stack;
21 мар 17, 14:56    [20317934]     Ответить | Цитировать Сообщить модератору
 Re: Древовидный несбалансированный справочник, как собрать факт  [new]
Ролг Хупин
Member

Откуда: Литва
Сообщений: 813
Wlr-l
1.Диалог:
- А алгоритм формирования lft и rgt в общем виде есть?
- Есть, описано в книгах и статьях Джо Селко.
- Я попросил конкретный SQL-запрос!

т.е. алгоритм = SQL-конкретный запрос!
Вообще-то алгоритмы обхода дерева описаны в учебниках по дискретной математике или программированию, если в них рассматриваются деревья. Джо Селко немного модифицировал один из них, предложив для каждой вершины два числа lft, rgt.

2.Я не предлагал NESTED SET, это сделал Джо Селко в прошлом веке. Я лишь показал, как решить эту задачу с помощью NESTED SET.

3.Решение с hierarchyid было дано ранее invm. Если включить столбец с hierarchyid в таблицу, то не нужно будет каждый раз его формировать, т.е. запрос а будет лишним.

4.hierarchyid не так прост, попробуйте переместить поддерево. Но это свойственно всем моделям представления деревьев реляционными таблицами. Кроме того рекурсия осталась. Hierarchyid, мне так кажется, произошел от "деревьев, не требующих поддержки", Итцика Бен-Гана.

5.Я вчера спешил и привел неполный пример. Приведу полный пример из статьи Джо Селко, немного модифицированный под T-SQL. Может быть синтаксис и устарел (это было давно), но пример можно скопировать, выполнить и посмотреть результат:
+
if object_id(N'personnel') is not null drop table personnel;
CREATE TABLE personnel (
 emp    CHAR(20)     PRIMARY KEY,
 boss   CHAR(20)     NULL REFERENCES Personnel(emp), 
 salary DECIMAL(6,2) NOT NULL
)
insert into personnel (emp,boss,salary) values('Jerry', NULL,   1000.00)
insert into personnel (emp,boss,salary) values('Bert',  'Jerry', 900.00)
insert into personnel (emp,boss,salary) values('Chuck', 'Jerry', 900.00)
insert into personnel (emp,boss,salary) values('Donna', 'Chuck', 800.00)
insert into personnel (emp,boss,salary) values('Eddie', 'Chuck', 700.00)
insert into personnel (emp,boss,salary) values('Fred',  'Chuck', 600.00)
select * from personnel;

if object_id(N'Tree') is not null drop table Tree;
CREATE TABLE Tree (
 emp  CHAR(10) NOT NULL,
 boss CHAR(10)     NULL
);

INSERT INTO Tree SELECT emp,boss FROM personnel;
select * from Tree;

-- Изначально стек пустой, он будет содержать модель вложенных множеств
if object_id(N'Stack') is not null drop table Stack;
CREATE TABLE Stack (
 stack_top INTEGER  NOT NULL,
 emp       CHAR(10) NOT NULL,
 lft       INTEGER      NULL,
 rgt       INTEGER      NULL
);

DECLARE @counter     INTEGER;
DECLARE @max_counter INTEGER;
DECLARE @current_top INTEGER;

SET @counter    =2;
SET @max_counter=2*(SELECT COUNT(*) FROM Tree);
SET @current_top=1;

INSERT INTO Stack SELECT 1,emp,1, @max_counter FROM Tree WHERE boss IS NULL;
DELETE FROM Tree WHERE boss IS NULL;

WHILE @counter<=(@max_counter-1) BEGIN
 IF EXISTS (SELECT * FROM Stack AS S1, Tree AS T1
             WHERE S1.emp=T1.boss AND S1.stack_top=@current_top)
    BEGIN --вершина имеет подчиненные вершины, заносим новое значение lft в стек
      INSERT INTO Stack
         SELECT (@current_top+1), MIN(T1.emp), @counter, NULL
         FROM Stack S1, Tree T1
         WHERE S1.emp=T1.boss AND S1.stack_top=@current_top;
      DELETE FROM Tree
         WHERE emp=(SELECT emp FROM Stack WHERE stack_top=@current_top+1);
      SET @counter=@counter+1;
      SET @current_top=@current_top+1;
    END
 ELSE BEGIN --выталкиваем значение из стека и определяем значение rgt
        UPDATE Stack
          SET rgt=@counter,
              stack_top=-stack_top -- pops the stack
          WHERE stack_top=@current_top
        SET @counter=@counter+1;
        SET @current_top=@current_top-1;
      END;
END;

select * from Stack;



2. Nested Sets придумал Камфонас, Целко популфризовал и развил
http://kamfonas.com/wordpress/wp-content/uploads/2007/08/recursive-hierarchies-no-columns.htm

4.hierarchyid не так прост, попробуйте переместить поддерево. - Перемещается, какие проблемы? не хуже, чем в нестед сетс
Но тип hierarchyid индексируется, и скорость выборок несравнима с самопальными ирерархиями.
21 мар 17, 17:11    [20318565]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить