Отправляет email-рассылки с помощью сервиса Sendsay

Вопросы и ответы по MS SQL Server

  Все выпуски  

Вопросы и ответы по MS SQL Server


Информационный Канал Subscribe.Ru


Вопросы и ответы по MS SQL Server

Выпуск No. 8 от 2003-04-10

Вопрос : Как пронумеровать строки в возвращаемом наборе данных?

Ответ:
В MS SQL Server нет встроенной функции для нумерации набора данных.

Вот что по этому поводу сказано на сервере www.mssqlserver.com в разделе FAQ:
"Нумерация строк не сопоставима с реляционными множествами (которые лежат в основе СУРБД SQL). Это не ANSI стандарт. Майкрософт не реализовала что-то подобное.
Если у Вас возникла необходимость в нумерации строк, Вам следует внимательно посмотреть на свое приложение и проанализировать, действительно ли эту функцию нужно переносить на серверную часть".
И далее никаких примеров не приводится. Сказали, как отрезали.
Тем не менее, в отделе поддержки компании Майкрософт есть HOWTO на эту тему, где кроме способов нумерации есть конкретные примеры, в которых показано для чего это может быть нужно. А нужно это чаще всего для построения отчетов.
Стало быть, все-таки бывает необходимость нумеровать строки в возвращаемом наборе данных.

Существует четыре принципиально различных способа нумерации строк. Чтобы посмотреть эти способы следует создать таблицу и определить выборку для нумерации:
CREATE TABLE Sales

(
SaleID int IDENTITY PRIMARY KEY CLUSTERED,
ClientID int,
Date datetime,
Amount money
)
insert Sales values(1,'20030301', 15.48)
insert Sales values(1,'20030302', 134.01)
insert Sales values(1,'20030303', 2346.03)
insert Sales values(2,'20030303', 754.88)
insert Sales values(3,'20030301', 73.07)
insert Sales values(3,'20030302', 734.46)
insert Sales values(4,'20030301', 1567.10)
insert Sales values(4,'20030304', 6575.70)
insert Sales values(4,'20030307', 6575.77)
insert Sales values(4,'20030309', 6575.37)
insert Sales values(5,'20030301', 1975.73)
insert Sales values(5,'20030306', 178965.63)
insert Sales values(6,'20030303', 16785.34)
insert Sales values(6,'20030304', 1705.44)
GO
select SaleID, ClientID, Date, SUM(Amount) as Amount
from Sales
where ClientID<4 and Date between '20030302' and '20030304'
group by SaleID, ClientID, Date

GO

Запрос несколько надуманный, но с помощью него можно показать все проблемы нумерации.
Способ 1.
Этот способ легче всего по реализации и проще для понимания. Он основан на показанной в предыдущей рассылке функции IDENTITY.
select IDENTITY(int) as ID, CAST(SaleID as int) as SaleID, ClientID, Date, SUM(Amount) as Amount

into #temp
from Sales
where ClientID<4 and Date between '20030302' and '20030304'
group by SaleID, ClientID, Date

select * from #temp
drop table #temp
GO

Результат:
ID          SaleID      ClientID    Date        Amount

----------- ----------- ----------- ----------- ---------
1 2 1 2003-03-02 134.0100
2 3 1 2003-03-03 2346.0300
3 4 2 2003-03-03 754.8800
4 6 3 2003-03-02 734.4600

(4 row(s) affected)

Пришлось работать с временной таблицей и повозиться с колонкой SaleID, т.к. в таблице не может быть больше одной колонки IDENTITY. Можете не волноваться насчет быстродействия, следующие способы будут еще хуже.
Способ 2.
Именно этот способ предлагают сотрудники компании Майкрософт. Хорош он тем, что все помещается в одном запросе.
SELECT COUNT(*) AS ID, a.SaleID, a.ClientID, a.Date, a.Amount

FROM
(
select SaleID, ClientID, Date, SUM(Amount) as Amount
from Sales
where ClientID<4 and Date between '20030302' and '20030304'
group by SaleID, ClientID, Date

) a,
(
select SaleID
from Sales
where ClientID<4 and Date between '20030302' and '20030304'
group by SaleID, ClientID, Date

) b
WHERE a.SaleID>=b.SaleID
GROUP BY a.SaleID, a.ClientID, a.Date, a.Amount
ORDER BY ID
GO

Результат запроса аналогичен предыдущему. Условие запроса пришлось повторить два раза. Текст запроса можно изменять и варьировать. Например, сделать вычисление ID на основе подзапроса в самой колонке, и это как правило работает быстрее.
Способ 3.
Этот способ подходит только для счастливых обладателей MS SQL 2000. Именно в этой версии появились пользовательские функции. Хотя возможность взаимодействия функций с базой данных сильно ограничена, тем не менее, существуют хитрые пути обхода ограничений, наложенных на функции. Следующий код показывает, как написать функцию для нумерации строк:
-- код для создания linked server

sp_addlinkedserver 'm'
GO
sp_setnetname 'm', '(local)'
GO
sp_serveroption 'm', 'data access', 'true'
GO
-- код для создания функции нумерации
CREATE TABLE TabRowNo (Name sysname, Date datetime, i int)
GO
CREATE PROCEDURE SetRowNo
AS
-- процедура сброса значения счетчика
SET NOCOUNT ON
DELETE TabRowNo WHERE Name=CURRENT_USER
INSERT TabRowNo VALUES(CURRENT_USER, getdate(), 0)
IF @@TRANCOUNT>0 COMMIT TRAN
SET NOCOUNT OFF
SELECT 0 as i
GO
CREATE PROCEDURE GetRowNo

AS
-- процедура получения следующего значения счетчика
SET NOCOUNT ON
UPDATE TabRowNo SET i=i+1 WHERE Name=CURRENT_USER
IF @@TRANCOUNT>0 COMMIT TRAN
SET NOCOUNT OFF
SELECT * FROM TabRowNo WHERE Name=CURRENT_USER
GO
CREATE FUNCTION RowNo (@Date datetime)
RETURNS int
AS
-- функция счетчика
BEGIN
declare @d datetime, @i int
SELECT @d=Date, @i=i FROM OPENQUERY(m, 'exec GetRowNo')

if @d is null or @d<@Date

begin
SELECT @i=i FROM OPENQUERY(m, 'exec SetRowNo')

SELECT @d=Date, @i=i FROM OPENQUERY(m, 'exec GetRowNo')

end
RETURN(@i)
END
GO

Поясню кратко, что написано в этом коде.
В первую очередь создается linked server для того же сервера, на котором Вы работаете. При этом следует учитывать, что при работе с функцией OPENQUERY будет создаваться отдельное подключение к базе данных "по умолчанию". Поэтому, либо создайте эти процедуры в базе "по умолчанию", либо измените код, вызываемый функцией OPENQUERY, либо добавьте опций при выполнении sp_addlinkedserver.
Далее создается таблица значений счетчиков для каждого пользователя.
Затем создаются процедуры установки и сброса счетчика, и сама функция возврата значения счетчика.
Скрипт для всех запросов с нумерацией строк будет предельно краток:
select dbo.RowNo(getdate())AS ID, SaleID, ClientID, Date, SUM(Amount) as Amount

from Sales
where ClientID<4 and Date between '20030302' and '20030304'
group by SaleID, ClientID, Date
GO

Возможность такого способа основана на том, что при выполнении приведенного выше скрипта, функция getdate выполняется один раз в самом начале, а функция RowNo для каждой строки.
У этого варианта есть масса недостатков. Во-первых, этот код работает очень медленно, т.к. для каждой строки запускается процедура. Во-вторых, для каждого пользователя одномоментно можно использовать всего один счетчик. В-третьих, очень сложно создаются процедуры и функция нумерации.
Но при этом, как просто в результат запроса добавлять номер строки!
И, тем не менее, способ интересен больше в познавательных целях, чем для практического применения.
Я не претендую на лаконичность решения. Может быть, у Вас получится гораздо лучше - пишите.
Способ 4.
Этот способ стоит использовать, если Вам не подходят предыдущие способы нумерации или если Вы хотите пронумеровать строки очень хитрым способом. Основан на особенностях оператора UPDATE в T-SQL.
declare @i int

set @i=0

select 0 as ID, SaleID, ClientID, Date, SUM(Amount) as Amount
into #temp
from Sales
where ClientID<4 and Date between '20030302' and '20030304'
group by SaleID, ClientID, Date


update #temp set @i=ID=@i+1

select * from #temp
drop table #temp

GO

Почему такая конструкция оператора UPDATE работает можно посмотреть в Books Online.

На этом закончим. Удалить созданные объекты можно с помощью следующего кода:
DROP TABLE Sales

GO
sp_dropserver 'm'
DROP TABLE TabRowNo
DROP FUNCTION RowNo
DROP PROCEDURE SetRowNo
DROP PROCEDURE GetRowNo
GO

АНОНС РАССЫЛКИ
Работа с MS Access

Рассылка посвящена созданию баз данных в MS Access. Освещаются общие
вопросы для новичков, поэтапное описание разработки примеров баз данных и
т.п. Для продвинутых разработчиков - углубленная работа с VBA, примеры
кодов и полезных функций, применение API, ActiveX и др.
Рассылки Subscribe.Ru
Работа с MS Access

Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru
С уважением, Сергей Кошкин.
Адрес сайта Рассылки - http://sql.boom.ru/

Архив на Subscribe.Ru
Поиск по архиву рассылки
"Вопросы и ответы по MS SQL Server"



Рейтинг@Mail.ru Rambler's Top100

http://subscribe.ru/
E-mail: ask@subscribe.ru
Отписаться
Убрать рекламу

В избранное