Сортировка набора данных в SQL Server иногда может быть вызовом, особенно когда критерии сортировки не хранятся в базе данных. В этой статье мы рассмотрим решение этой проблемы с использованием связанных списков в SQL Server.

Допустим, у нас есть таблица песен с первичным ключом, называемым Song_ID. Мы хотим создать плейлисты с определенными песнями в определенном порядке. Для этого мы создаем другую таблицу, которая содержит данные плейлиста, включая названия плейлистов. Мы могли бы создать отдельную таблицу для моделирования отношения многие-ко-многим между песнями и плейлистами, но мы также хотим сохранить порядок песен в каждом плейлисте.

Один из подходов – добавить столбец позиции в таблицу, который хранит позицию каждой песни в плейлисте. Однако это решение может стать неэффективным, когда нам нужно добавлять, удалять или перемещать песни внутри плейлиста. Каждая операция требует нескольких обновлений для поддержания непрерывной нумерации столбца позиции.

Лучшим решением является использование структуры связанного списка. Вместо столбца позиции мы создаем столбец с именем Predecessor, который указывает на Song_ID предыдущей песни в плейлисте. Первая песня в плейлисте имеет NULL предшественника. Таблица будет выглядеть так:

Playlist_Contents с столбцом Predecessor
Playlist_ID | Song_ID | Predecessor
1           | 20      | NULL
1           | 50      | 20
1           | 60      | 50
1           | 30      | 60
1           | 40      | 30
1           | 10      | 40

Чтобы получить позицию каждой песни в плейлисте, мы можем создать представление, которое генерирует номер позиции динамически с использованием рекурсивного общего выражения (CTE). Представление будет выглядеть так:

CREATE VIEW [dbo].[vPlaylist_Contents] AS
WITH List AS (
SELECT Playlist_ID, 1 AS Position, Song_ID, Predecessor
FROM Playlist_Contents
WHERE Predecessor IS NULL
UNION ALL
SELECT p.Playlist_ID, l.Position + 1, p.Song_ID, p.Predecessor
FROM Playlist_Contents p
INNER JOIN List l ON p.Playlist_ID = l.Playlist_ID AND p.Predecessor = l.Song_ID
)
SELECT * FROM List

Изменение последовательности песен в плейлисте просто с этой структурой связанного списка. Чтобы добавить новую песню или изменить позицию песни, мы просто обновляем столбец Predecessor, чтобы указать на желаемую предшествующую песню или устанавливаем его в NULL для первой песни. Триггер на представлении выполняет необходимые дополнительные модификации в таблице для поддержания целостности структуры связанного списка.

Это решение сокращает количество строк, которые необходимо изменить по сравнению с подходом с использованием столбца позиции. Оно имеет верхнюю границу в 2 для вставок, 2 для удалений и 3 для обновлений, независимо от количества песен в плейлисте.

Однако есть некоторые ограничения, которые следует учесть. Эта реализация не позволяет изменять значения первичного ключа строки, такие как замена песни другой или перемещение песни из одного плейлиста в другой. Чтобы обработать эти сценарии, вы можете расширить таблицу с помощью замещающего первичного ключа и использовать его в триггере.

В заключение, использование связанных списков в SQL Server может быть эффективным решением для сортировки наборов данных, когда критерии сортировки не хранятся в базе данных. Используя структуру связанного списка и триггер на представлении, мы можем поддерживать порядок песен в плейлисте с минимальными модификациями таблицы.