Построение всех возможных комбинаций с помощью T-SQL

Забава или рутина?

Построение всех возможных комбинаций(удовлетворяющих набору условий) это задача высосана из пальца или рутина? Давайте представим, что вы работаете в компании, которая предоставляет какие-то услуги своим клиентам, как правило компании продает не одну услугу, а целый набор. Например, покупая летом путевку в жаркую страну, вы наверняка приобретете следующее

• Авиабилет в страну отдых, то есть билет эконом-класса на %airlines%.
• Трансфер до отеля, то есть поездка на большом автобусе с еще 50 туристами.
• Проживание в отеле, то есть 14 ночей в %hotel% в номере с видом на сад.
• Трансфер до аэропорта, то есть еще одна поездка в автобусе.
• Авиабилет домой, возвращение обратным рейсом той же %airlines%.

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

Опишем эту же задачу с помощью кода.

--Классы услуг
CREATE TABLE [Classes] (
  [id] [int] NOT NULL PRIMARY KEY
 ,[name] varchar(50) NOT NULL
);
GO
INSERT INTO [Classes] VALUES (1,'Авиаперелет');
INSERT INTO [Classes] VALUES (2,'Трансфер');
INSERT INTO [Classes] VALUES (3,'Проживание');

--Список туров
CREATE TABLE [Tours](
  [id] [int] NOT NULL PRIMARY KEY
 ,[name] varchar(50) NOT NULL
);
GO
INSERT INTO [Tours] VALUES (1, 'Тур');

--Тур состоит набора услуг
--В данном случае из тех же 5, что приведены в примере
CREATE  TABLE [TourDetails] (
  [id] [int] NOT NULL PRIMARY KEY
 ,[tour] [int] NOT NULL
 ,[class] [int] NOT NULL
);
GO
INSERT INTO [TourDetails] VALUES (1,1,1)
INSERT INTO [TourDetails] VALUES (2,1,2)
INSERT INTO [TourDetails] VALUES (3,1,3)
INSERT INTO [TourDetails] VALUES (4,1,2)
INSERT INTO [TourDetails] VALUES (5,1,1)

-- Справочник услуг.
--Рассмотрим простую систему, где все услуги хранятся в одной табличке
CREATE TABLE [Services](
  [id] [int] NOT NULL PRIMARY KEY
 ,[name] varchar(50)
);
GO
INSERT INTO [Services] VALUES (1, 'Эконом класс');
INSERT INTO [Services] VALUES (2, 'Бизнес класс');

INSERT INTO [Services] VALUES (3, 'Групповой трансфер');
INSERT INTO [Services] VALUES (4, 'Номер с видом на бассейн');

--И последнее
--Привязка услуги к классу услуг.
CREATE TABLE [ServiceClasses](
  [id] [int] NOT NULL PRIMARY KEY
 ,[class] [int] NOT NULL
 ,[service] [int] NOT NULL
);
GO
INSERT INTO [ServiceClasses] VALUES (1, 1, 1);
INSERT INTO [ServiceClasses] VALUES (2, 1, 2);
INSERT INTO [ServiceClasses] VALUES (3, 2, 3);
INSERT INTO [ServiceClasses] VALUES (4, 3, 4);

А получить мы хотим все перестановки для данного тура(туров), то есть

id тура     id комбинации id деталей тура id услуги
----------- ------------- --------------- -----------
1           1             1               1
1           1             2               3
1           1             3               4
1           1             4               3
1           1             5               1
-----------------------------------------------------
1           2             1               2
1           2             2               3
1           2             3               4
1           2             4               3
1           2             5               1
-----------------------------------------------------
1           3             1               1
1           3             2               3
1           3             3               4
1           3             4               3
1           3             5               2
-----------------------------------------------------
1           4             1               2
1           4             2               3
1           4             3               4
1           4             4               3
1           4             5               2

Посмотрим на результаты чуть внимательней. В первую колонку попадает id Тура([Tours].[id]), так как он у нас всего один, то значение всегда равно 1. Во второй колонке id комбинации, для каждой группы данных/набора оно свое. В третей колонке хранятся идентификаторы деталей тура, то есть [TourDetails].[id]. И в последней идентификаторы услуг. Таким образом, в первой комбинации оба перелета эконом класса, во второй и третей по одному перелету эконом класса, а в последней и туда и обратно турист полетит бизнес классом.

А теперь самое интересное, как сделать это с помощью T-SQL? Ответ и немного кода можно найти под катом.