SQLServer SQL-запрос с счетчиком строк

У меня есть SQL-запрос, который возвращает набор строк:

SELECT id, name FROM users where group = 2

Мне нужно также включить столбец с добавочным целым значением, поэтому в первой строке должно быть 1 столбец счетчика, второй - 2, третий - 3 и т.д.

Запрос, показанный здесь, является просто упрощенным примером, на самом деле запрос может быть произвольно сложным, с несколькими объединениями и вложенными запросами.

Я знаю, что это может быть достигнуто с использованием временной таблицы с полем autonumber, но есть ли способ сделать это в самом запросе?

Ответ 1

Для начала, что-то по строкам:

SELECT my_first_column, my_second_column,
    ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table

Однако важно отметить, что конструкция ROW_NUMBER() OVER (ORDER BY ...) определяет только значения Row_Counter, это не гарантирует упорядочение результатов.

Если сам SELECT имеет явное предложение ORDER BY, результаты могут быть возвращены в любом порядке, в зависимости от того, как SQL Server решает оптимизировать запрос. (Подробнее см. в этой статье.)

Единственный способ гарантировать, что результаты всегда будут возвращены в порядке Row_Counter, - это применить точно такое же упорядочение как к SELECT, так и к ROW_NUMBER():

SELECT my_first_column, my_second_column,
    ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table
ORDER BY my_order_column  -- exact copy of the ordering used for Row_Counter

Вышеприведенный шаблон всегда будет возвращать результаты в правильном порядке и хорошо работает для простых запросов, но как насчет "произвольно сложного" запроса, возможно, с десятками выражений в предложении ORDER BY? В таких ситуациях я предпочитаю нечто подобное:

SELECT t.*
FROM
(
    SELECT my_first_column, my_second_column,
        ROW_NUMBER() OVER (ORDER BY ...) AS Row_Counter  -- complex ordering
    FROM my_table
) AS t
ORDER BY t.Row_Counter

Использование вложенного запроса означает, что нет необходимости дублировать сложное предложение ORDER BY, что означает меньшее количество беспорядков и упрощение обслуживания. Внешний ORDER BY t.Row_Counter также делает задачу запроса более понятной для ваших разработчиков-разработчиков.

Ответ 2

В SQL Server 2005 и выше вы можете использовать функцию ROW_NUMBER(), которая имеет параметры для порядка сортировки и групп которые подсчитываются (и reset).

Ответ 3

Самый простой способ - использовать счетчик переменных строк. Однако это были бы две фактические команды SQL. Один для установки переменной, а затем запрос следующим образом:

SET @n=0;
SELECT @n:[email protected]+1, a.* FROM tablename a

Ваш запрос может быть таким же сложным, как вам нравится, с объединениями и т.д. Я обычно делаю это хранимой процедурой. Вы можете иметь все виды удовольствия с переменной, даже использовать ее для вычисления значений полей. Ключом является :=

Ответ 4

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

Пример:

create table #test (
        rowcounter int identity,
        invoicenumber varchar(30)
        )

insert into #test(invoicenumber) select [column] from [Table1]

insert into #test(invoicenumber) select [column] from [Table2]

insert into #test(invoicenumber) select [column] from [Table3]

select * from #test

drop table #test