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

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

Подписаться Бесплатная новостная рассылка Подписчиков 17.253 RSS
←   Апрель 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


За последние 60 дней 1 выпусков (1 раз в 2 месяца)


Сайт рассылки: http://www.itnews.ru
Открыта: 04-03-2003

Автор
Сергей

Статистика

17.253 подписчиков
-3 за неделю
←   Все выпуски   →

Вопросы и ответы по 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 		Отписаться
Убрать рекламу
В избранное