вторник, 28 апреля 2009 г.

Как в SQL Server отсортировать иерархический рекурсивный запрос?

В SQL Server 2005 появилось нововведение в виде CTE (common table expressions), которое позволяет выполнять иерархические рекурсивные запросы к базе данных.
При разработке сайта Калькулятор на автокредит у меня появилась задача: отобразить иерархическую структуру страниц сайта, причем отсортированную по полю pageorder у страниц с одинаковым уровнем иерархии.

Структура таблицы выглядит примерно так:


CREATE TABLE [dbo].[pages](
[id] [int] NULL,
[pid] [int] NULL,
[title] [nvarchar](max) NULL,
[pageorder] [int] NULL
) ON [PRIMARY]



Поле pid указывает на id родителя данной записи. Кроме того должна еще существовать одна root-запись, у которой pid = NULL.

Итак сам запрос выглядит таким образом:


WITH CTE(pid, id, title, level, pageorder, pathstr) AS
(
SELECT pid, id, title, 0, 0, CAST ('/' AS VARCHAR(MAX))
FROM pages
WHERE pid IS NULL
UNION ALL
SELECT e.pid, e.id, e.title, level + 1, e.pageorder, d.pathstr + CAST(e.pageorder as VARCHAR) + '/'
FROM pages e
INNER JOIN CTE d
ON e.pid = d.id

)
SELECT pid, id, replicate('... ', level) + title as title, level, pageorder, pathstr
FROM CTE
ORDER BY pathstr


Вот что мы получим после выполнения этого запроса:


pid id level pageorder pathstr title title
----------- ----------- ----------- ----------- --------- ------------------------------
NULL 1 0 0 / Главная
1 4 1 3 /3/ ... Товары
4 5 2 3 /3/3/ ... ... автомобили
4 8 2 6 /3/6/ ... ... мотоциклы
1 2 1 6 /6/ ... Услуги
2 6 2 3 /6/3/ ... ... Лизинг
2 3 2 6 /6/6/ ... ... Кредитование
1 7 1 9 /9/ ... О сайте

(8 row(s) affected)


Стоит также отметить, что в SQL Server 2008 появился новый тип данных hierarchyid, позволяющий работать с иерархическими данными намного проще и удобнее.