Нумерация строк таблицы

Думаю всем программистам баз данных известна группа задач, в которых при определенных условиях требуется запросом получить набор всех записей некоторой таблицы, вместе со столбом, содержащим номер строки. То есть, чтобы в выходном наборе строки были пронумерованы последовательными числами, начиная с 1.

Итак, что же известно и методах решения таких задач. Начиная с MS SQL Server 2005, довольно легко, используя функцию ранжирования row_number, решить задачу о нумерации всех строк любой таблицы. Благодаря этой новинке от Microsoft, можно не создавать для нумерации временные таблицы с identity-полями, не мучиться с курсорами, в общем, в реальном программировании задача стала простой. Но что если программиста попросили решить задачу с помощью голого select-а (то есть без всяких вспомогательных объектов, вроде временных таблиц или циклов), да еще и при помощи средств MS SQL Server 2000.

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

Итак, сформулируем точное условие задачи.

Дана таблица dbo.Customers:

create table dbo.Customers

(

      Name    nvarchar ( 100 ) not null,

      SurName nvarchar ( 100 ) not null,

      constraint PK_Customers_Name_SurName primary key clustered ( Name asc, SurName asc )

) on [PRIMARY]

Вставим в нее несколько записей:

insert into dbo.Customers ( Name, SurName )

      values

            ( N'Сергей', N'Петров' ),

            ( N'Сергей', N'Иванов' ),

            ( N'Андрей', N'Петров' )

Заметим, что в таблице нет уникальности ни по имени, ни по фамилии. Есть уникальность по двум этим столбцам.

Напишем и выполним такой запрос:

select custGreater.Name, custGreater.SurName,

      case when min ( custLess.Name ) is null then 1 else 1 + count (*) end as  Number

from

      dbo.Customers custGreater

            left outer join

      dbo.Customers custLess on

            custGreater.Name > custLess.Name or

            (

                  custGreater.Name = custLess.Name and

                  custGreater.SurName > custLess.SurName

            )

group by custGreater.Name, custGreater.SurName

Давайте немного обсудим полученное решение. Здесь происходит левое соединение двух экземпляров таблицы. Но при этом в условии соединения используется не предикат равенства, а условие на то, что кортеж из двух элементов (имя и фамилия) левой таблицы больше по лексикографическому порядку кортежа таблицы справа. С помощью оператора group by и функции count происходит подсчет для каждой пары таблицы слева числа пар таблицы справа, меньших чем пара левой таблицы. Если пара левой таблицы является минимальной, то для нее есть только одна пустая запись от таблицы справа, что проверяется выражением case when.

Подобным образом задачу можно было бы решить через связанный подзапрос:

select cust.Name, cust.SurName,

      (

            select count (*) + 1

            from dbo.Customers

            where

                  cust.Name > Name or

                  (

                        cust.Name = Name and

                        cust.SurName > SurName

                  )

      )as Number

from dbo.Customers cust

А что если в таблице есть идентичные строки, то есть нет уникальности даже по всем наборам столбцам. У меня сложилось впечатление, что в этом случае, решения одним запросом средствами MS SQL Server 2000 нет. Сделаем тогда одно естественное допущение: предположим, что число дублей ограничено некоторым известным числом. То есть, например, если в описанной выше таблице есть строка ( 'Андрей', 'Петров' ), то существует еще не более чем, скажем 2 точно таких же строки. В этом случае задачу решить можно. Итак, будем предполагать, что общее число одинаковых строк не больше чем 3 для любой строки. Наполним заново таблицу dbo.Customers:

delete dbo.Customers

alter table dbo.Customers drop constraint PK_Customers_Name_SurName

insert into dbo.Customers ( Name, SurName )

      values

            ( N'Андрей', N'Иванов' ),

            ( N'Сергей', N'Иванов' ),

            ( N'Сергей', N'Иванов' ),

            ( N'Сергей', N'Петров' ),

            ( N'Сергей', N'Петров' ),

            ( N'Сергей', N'Петров' )

Перед началом решения немного обсудим общую идею. У нас нет столбцов, по которым была бы уникальность. Так давайте создадим их. В этом поможет исходное допущение и оператор cross join:

select Cust.Name, Cust.SurName, Cust.Number Number1, PrmData.Number Number2

from

      (

            select Name, SurName, count (*) as Number

            from dbo.Customers

            group by Name, SurName

      ) Cust

            cross join

      (

            select 1 as Number

                  union all

            select 2

                  union all

            select 3

      ) PrmData

where PrmData.Number <= Cust.Number

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

select dataNum.Name, dataNum.SurName,

      (

            select count (*) + 1

            from

            (

                  select Cust.Name, Cust.SurName, Cust.Number Number1,

                     PrmData.Number Number2

                  from

                        (

                             select Name, SurName, count (*) as Number

                             from dbo.Customers

                             group by Name, SurName

                        ) Cust

                             cross join

                        (

                             select 1 as Number

                                   union all

                             select 2

                                   union all

                             select 3

                        ) PrmData

                  where PrmData.Number <= Cust.Number

            ) data

            where

                  Number1 < dataNum.Number1 or

                  (

                        Number1 = dataNum.Number1 and

                        Number2 < dataNum.Number2

                  )

      ) as Number

from

(

      select data.Name, data.SurName, data.Number1, data.Number2

      from

      (

            select Cust.Name, Cust.SurName, Cust.Number Number1,

                PrmData.Number Number2

            from

                  (

                        select Name, SurName, count (*) as Number

                        from dbo.Customers

                        group by Name, SurName

                  ) Cust

                        cross join

                  (

                        select 1 as Number

                             union all

                        select 2

                             union all

                        select 3

                  ) PrmData

            where PrmData.Number <= Cust.Number

      ) data

) dataNum