есть табличка справочника, типа есть группы в них группы или товары (как файлы-папки):
CREATE TABLE GOOD ( --товары
ID Integer NOT NULL, --первичный ключ
LEFTBOUND Integer NOT NULL,
RIGHTBOUND Integer NOT NULL,
PARENT Integer,--сылка на группу владельца, для первого уровня NULL
NAME Varchar(50) NOT NULL, --наименование группы или товара
ISGROUP Integer NOT NULL--признак группы: 0,1
);
правило: если товар А лежит в группе Б а та в группе С то:
C.LEFTBOUND<B.LEFTBOUND<A.LEFTBOUND=A.RIGHTBOUND<=B.RIGHTBOUND<=C.RIGHTBOUND
то есть диапозон LEFTBOUND ... RIGHTBOUND групп охватывает вложенные записи (групп и товаров)
для товаров (не групп) верно: LEFTBOUND=RIGHTBOUND
требуется написать запрос, упорядочивающий записи по иерархии, а среди записей одной ветки иерархии должно быть упорядочивание по (IsGroup,Name), например:
Группа 1
__Группа 1.1
____Товар 1.1.1
____Товар 1.1.2
__Товар 1.2
__Товар 1.3
Группа 2
__Группа 2.1
__Группа 2.2
вот такой запрос, дает иерархию, но не дает упорядоченности внутри веток иерархии:
SELECT g.ID, g.Name, g.ISGROUP, count(g2.ID) AS glevel
FROM GOOD g
LEFT JOIN GOOD g2 ON (g.LEFTBOUND BETWEEN g2.LEFTBOUND AND g2.RIGHTBOUND)
GROUP BY g.ID, g.Name, g.ISGROUP, g.LEFTBOUND
ORDER BY g.LEFTBOUND
поле glevel отвечает за уровень вложенности записи
Во второй статье тоже, что у меня, ответа по упорядочиванию внутри одной ветки иерархии там нет, к сожалению
Собственно, я конечно брал это из подобных источников
Здравствуйте, naf2000, Вы писали:
N>есть табличка справочника, типа есть группы в них группы или товары (как файлы-папки): N>
CREATE TABLE GOOD ( --товары
N> ID Integer NOT NULL, --первичный ключ
N> LEFTBOUND Integer NOT NULL,
N> RIGHTBOUND Integer NOT NULL,
N> PARENT Integer,--сылка на группу владельца, для первого уровня NULL
N> NAME Varchar(50) NOT NULL, --наименование группы или товара
N> ISGROUP Integer NOT NULL--признак группы: 0,1
N>);
N>правило: если товар А лежит в группе Б а та в группе С то: N>C.LEFTBOUND<B.LEFTBOUND<A.LEFTBOUND=A.RIGHTBOUND<=B.RIGHTBOUND<=C.RIGHTBOUND
Здравствуйте, Flying Dutchman, Вы писали:
FD>Здравствуйте, naf2000, Вы писали:
N>>есть табличка справочника, типа есть группы в них группы или товары (как файлы-папки): N>>
CREATE TABLE GOOD ( --товары
N>> ID Integer NOT NULL, --первичный ключ
N>> LEFTBOUND Integer NOT NULL,
N>> RIGHTBOUND Integer NOT NULL,
N>> PARENT Integer,--сылка на группу владельца, для первого уровня NULL
N>> NAME Varchar(50) NOT NULL, --наименование группы или товара
N>> ISGROUP Integer NOT NULL--признак группы: 0,1
N>>);
N>>правило: если товар А лежит в группе Б а та в группе С то: N>>C.LEFTBOUND<B.LEFTBOUND<A.LEFTBOUND=A.RIGHTBOUND<=B.RIGHTBOUND<=C.RIGHTBOUND
FD>Используемая СУБД ?
На данный момент это скорее экперимент. Использую Firebird. Решение хотелось бы иметь достаточно универсальное (переносимое на другие СУБД). Чего точно не хотелось бы: рекурсий в stored procedure, с рекурсией все понятно.
FD>>Здравствуйте, naf2000, Вы писали:
FD>>Используемая СУБД ? N>На данный момент это скорее экперимент. Использую Firebird. Решение хотелось бы иметь достаточно универсальное (переносимое на другие СУБД). Чего точно не хотелось бы: рекурсий в stored procedure, с рекурсией все понятно.
Для SQL Server 2005/2008 можно написать рекурсивный запрос с использованием CTE.
без специфики сервера (то есть максимально переносимо) получилось вот так:
SELECT
T.ID1 ID ,T.NAME1 NAME, COUNT(DISTINCT GLEV.ID) LEV
FROM
(SELECT G1.ID ID1, G2.ID ID2, MAX(MP.LEFTBOUND) MPL, MIN(MP.RIGHTBOUND) MPR, G1.LEFTBOUND GL1, G2.LEFTBOUND GL2, G1.NAME NAME1
FROM GOOD G1
LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID)
LEFT JOIN GOOD MP ON ((G1.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND) AND (G2.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND))
GROUP BY G1.ID, G2.ID, G1.LEFTBOUND, G2.LEFTBOUND, G1.NAME) T
LEFT JOIN GOOD MP0 ON (MP0.LEFTBOUND = T.MPL)
LEFT JOIN GOOD GI1 ON ((GI1.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI1.PARENT IS NULL))) AND (T.GL1 BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND)
LEFT JOIN GOOD GI2 ON ((GI2.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI2.PARENT IS NULL))) AND (T.GL2 BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND)
LEFT JOIN GOOD GLEV ON (T.GL1 BETWEEN GLEV.LEFTBOUND AND GLEV.RIGHTBOUND)
GROUP BY T.ID1, T.NAME1
ORDER BY
SUM(
CASE
WHEN T.ID1=MP0.ID THEN 1
WHEN T.ID2=MP0.ID THEN 0
WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1
WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0
WHEN (GI1.NAME<GI2.NAME) THEN 1
ELSE 0
END) DESC
ORDER BY
COUNT( DISTINCT
CASE
WHEN T.ID1=MP0.ID THEN ID2
WHEN T.ID2=MP0.ID THEN NULL
WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN ID2
WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN NULL
WHEN (GI1.NAME<GI2.NAME) THEN ID2
ELSE NULL
END)
naf2000 пишет: > На данный момент это скорее экперимент. Использую Firebird. Решение > хотелось бы иметь достаточно универсальное (переносимое на другие СУБД).
Универсального решения на чистом SQL для этой задачи (обход дерева) нет.
На SQL она вообще не решается. (надеюсь, что SQL не является тьюринг-полным,
для тебя не новость).
Либо пишешь CONNECT ... BY или аналоги,
либо пишешь хранимку на процедурном языке,
либо применяешь специальные структуры данных, где хранишь
результаты обхода дерева, выполненные при модификации данных.
naf2000 пишет:
> без специфики сервера (то есть максимально переносимо) получилось вот так: > > > SELECT > T.ID1 ID ,T.NAME1 NAME, COUNT(DISTINCT GLEV.ID) LEV > FROM
....
"Исхитрись-ка мне добыть
то, чаво не может быть !"
SELECT
G1.ID ID ,G1.NAME NAME,
SUM(
CASE WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 1
ELSE 0
END) LEV
FROM GOOD G1
LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID)
LEFT JOIN GOOD GI1 ON (G1.LEFTBOUND BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND)
LEFT JOIN GOOD GI2 ON (G2.LEFTBOUND BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND)
WHERE
((GI1.PARENT=GI2.PARENT) OR ((GI1.PARENT IS NULL)AND(GI2.PARENT IS NULL))) AND
(NOT(GI1.ID=GI2.ID) OR (G1.ID=GI1.ID) OR (G2.ID=GI2.ID))
GROUP BY G1.ID, G1.NAME
ORDER BY
SUM(
CASE
WHEN G2.LEFTBOUND BETWEEN G1.LEFTBOUND AND G1.RIGHTBOUND THEN 1
WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 0
WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1
WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0
WHEN (GI1.NAME<GI2.NAME) THEN 1
ELSE 0
END) DESC
Наконец, допустим есть таблица продаж (очень упрощенно)
CREATE TABLE SALE (
ID Integer NOT NULL,
GOOD Integer NOT NULL,
ACOUNT Numeric(15,3) NOT NULL,
CONSTRAINT PK_SALE PRIMARY KEY (ID)
);
ALTER TABLE SALE ADD CONSTRAINT FK_SALE_1 FOREIGN KEY (GOOD) REFERENCES GOOD(ID);
И нам необходимо вывести продаваемые продукты по-иерархии с требуемой сортировкой:
SELECT T.ID ID ,T.NAME NAME, T.LEV LEV, SUM(S.ACOUNT) ACOUNT
FROM SALE S
INNER JOIN GOOD G ON (S.GOOD=G.ID)
INNER JOIN
(SELECT
G1.ID ID ,G1.NAME NAME, G1.LEFTBOUND LEFTBOUND, G1.RIGHTBOUND RIGHTBOUND,
SUM(
CASE WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 1
ELSE 0
END) LEV,
SUM(
CASE
WHEN G2.LEFTBOUND BETWEEN G1.LEFTBOUND AND G1.RIGHTBOUND THEN 1
WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 0
WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1
WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0
WHEN (GI1.NAME<GI2.NAME) THEN 1
ELSE 0
END) ORD
FROM GOOD G1
LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID)
LEFT JOIN GOOD GI1 ON (G1.LEFTBOUND BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND)
LEFT JOIN GOOD GI2 ON (G2.LEFTBOUND BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND)
WHERE
((GI1.PARENT=GI2.PARENT) OR ((GI1.PARENT IS NULL)AND(GI2.PARENT IS NULL))) AND
(NOT(GI1.ID=GI2.ID) OR (G1.ID=GI1.ID) OR (G2.ID=GI2.ID))
GROUP BY G1.ID, G1.NAME, G1.LEFTBOUND, G1.RIGHTBOUND) T
ON ((G.ID=T.ID) OR (G.LEFTBOUND BETWEEN T.LEFTBOUND AND T.RIGHTBOUND))
GROUP BY T.ID,T.NAME,T.LEV, T.ORD
ORDER BY T.ORD DESC
N>требуется написать запрос, упорядочивающий записи по иерархии, а среди записей одной ветки иерархии должно быть упорядочивание по (IsGroup,Name), например:
N>Группа 1 N>__Группа 1.1 N>____Товар 1.1.1 N>____Товар 1.1.2 N>__Товар 1.2 N>__Товар 1.3 N>Группа 2 N>__Группа 2.1 N>__Группа 2.2
N>вот такой запрос, дает иерархию, но не дает упорядоченности внутри веток иерархии: N>
SELECT g.ID, g.Name, g.ISGROUP, count(g2.ID) AS glevel
N>FROM GOOD g
N>LEFT JOIN GOOD g2 ON (g.LEFTBOUND BETWEEN g2.LEFTBOUND AND g2.RIGHTBOUND)
N>GROUP BY g.ID, g.Name, g.ISGROUP, g.LEFTBOUND
N>ORDER BY g.LEFTBOUND
N>поле glevel отвечает за уровень вложенности записи
Я не так давно реализовал иерархии в SQL 2005 на CTE.
Вот кусок кода
create function dbo.fn_get_all_custom_hierarchy_nodes_recursive(@hierarchy_id int, @level int)
returns table
as
return
with tree ([id], [level], [name], [parent_id], [top_node_id], [description], path_symbolic, path_numeric) as (
select [id]
, [level]
, [name]
, [parent_id]
, [id] as [top_node_id]
, [description]
, convert(nvarchar(max), [name]) as path_symbolic
, convert(nvarchar(max), [id]) as path_numeric
from dbo.custom_hierarchy_nodes
where [hierarchy_id] = @hierarchy_id
and [level] = @level
union all
select n.[id]
, n.[level]
, n.[name]
, n.[parent_id]
, t.[top_node_id]
, n.[description]
, t.path_symbolic + N'/' + n.[name] as path_symbolic
, t.path_numeric + N'/' + convert(nvarchar(max), n.[id]) as path_numeric
from dbo.custom_hierarchy_nodes n
, tree t
where n.parent_id = t.[id]
)
select id
, [level]
, [name]
, [parent_id]
, [top_node_id]
, [description]
, path_symbolic
, path_numeric
from tree;
а вот запрос с сортировкой по имени (в вашем случае отсортируете по другому атрибуту)
select *
from dbo.fn_get_all_custom_hierarchy_nodes_recursive(1,1)
order by [level]
, parent_id
, [name]
То есть сортировка по уровню, содержащему узлу и атрибуту элемента (в моем случае — имени).
а если вам надо чтобы <children> шли непосредственно за <parent> тогда можно сортировать или по <path> или распрямить идентификаторы уровнеы (на фиксированную глубину) и сортировать по ним последовательно <order by top_node_id, parent_level_2_id, parent_level_3_id,..., name>
Но вообще это наверное должен делать <report>.