В настоящее время вы работаете в автономном режиме; ожидается повторное подключение к Интернету

Динамическая нумерация строк в запросе SELECT языка Transact-SQL

Аннотация
В данной статье рассказывается о том, как динамически ранжировать строки при выполнении запроса SELECT с помощью гибкого способа, который отличается более высоким быстродействием в сравнении с процедурным решением и может оказаться единственным возможным способом выполнения данной задачи. Нумерацию и ранжирование строк обычно выполняют с помощью процедур. Эти решения обычно основаны на использовании циклов и временных таблиц, то есть циклов и курсоров SQL Server. Предлагаемая в данной статье методика основана на автообъединении, при этом обычно выбирается отношение «больше, чем» и определяется, сколько раз каждый элемент конкретного множества данных удовлетворяет отношению «больше, чем» при сравнении множества с самим собой.

Примечание. Приведенные ниже примеры основаны на использовании базы данных pubs. В SQL Server 2005 базы данных Northwind и pubs по умолчанию не устанавливаются. Их можно загрузить с веб-узла центра загрузки Майкрософт. Дополнительные сведения см. на веб-узле Майкрософт по следующему адресу:
http://www.microsoft.com/en-us/download/details.aspx?id=23654 (эта ссылка может указывать на содержимое полностью или частично на английском языке).
После загрузки файла SQL2000SampleDb.msi дважды щелкните его, чтобы извлечь сценарии баз данных. По умолчанию сценарии баз данных и файл сведений извлекаются из файла SQL2000SampleDb.msi в следующую папку:
C:\SQL Server 2000 Sample Databases
Выполните сценарии установки, следуя инструкциям в файле сведений.

Сведения для пользователей SQL Server 2005

Корпорация Майкрософт рекомендует использовать ранжирующие функции — новые функции SQL Server 2005. Дополнительную информацию об этих функциях можно узнать на следующем веб-сайте Microsoft Developer Network (MSDN):К началу статьи

Пример 1

Сведения о примере.
  • Множество 1 представляет сведения об авторах.
  • Множество 2 представляет сведения об авторах.
  • Отношение формулируется как «имена и фамилии больше, чем».
  • Проблему дублирования данных можно предотвратить, сравнивая сочетания имени и фамилии с другими сочетаниями имени и фамилии.
  • Для определения того, сколько раз удовлетворяется отношение, используется функция count(*).
Запрос:
   select rank=count(*), a1.au_lname, a1.au_fname   from authors a1, authors a2   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname   group by a1.au_lname, a1.au_fname   order by rank				
При работе с SQL Server 2005 используйте следующий код.
   select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname   from authors a   order by rank  
Результат:
   Rank        Au_Lname              Au_Fname   ----        --------------        -----------     1         Bennet                Abraham     2         Blotchet-Halls        Reginald     3         Carson                Cheryl     4         DeFrance              Michel     5         del Castillo          Innes     6         Dull                  Ann     7         Greene                Morningstar     8         Green                 Marjorie     9         Gringlesby            Burt    10         Hunter                Sheryl    11         Karsen                Livia    12         Locksley              Charlene    13         MacFeather            Stearns    14         McBadden              Heather    15         O'Leary               Michael    16         Panteley              Sylvia    17         Ringer                Albert    18         Ringer                Anne    19         Smith                 Meander    20         Straight              Dean    21         Stringer              Dirk    22         White                 Johnson    23         Yokomoto              Akiko   (23 row(s) affected)				
К началу статьи

Пример 2

Сведения о примере.
  • В данном примере выполняется ранжирование магазинов по числу проданных книг.
  • Множество 1 представляет число книг, проданных магазином: select stor_id, qty=sum(qty) from sales group by stor_id.
  • Множество 2 представляет число книг, проданных магазином: select stor_id, qty=sum(qty) from sales group by stor_id.
  • Отношение формулируется как «число книг больше, чем».
  • Во избежание дублирования данных можно (например) сравнивать значения price*qty, а не qty.
Запрос:
   select rank=count(*), s1.stor_id, qty=sum(s1.qty)   from (select stor_id, qty=sum(qty) from sales group by stor_id) s1,        (select stor_id, qty=sum(qty) from sales group by stor_id) s2   where s1.qty >= s2.qty   group by s1.stor_id   order by rank				
Результат:
   Rank     Stor_Id    Qty   ----     -------    ---   1         6380        8   2         7896      120   3         8042      240   4         7067      360   5         7066      625   6         7131      780   (6 row(s) affected)				
Примечание. Значения в столбце Qty ошибочны, однако ранжирование магазинов по числу проданных книг выполняется правильно. Это недостаток данного способа. Можете использовать этот способ для ранжирования магазинов, если правильность выходных данных о количестве не имеет значения.

При работе с SQL Server 2005 используйте следующий код.
select row_number() over (order by qty desc) as rank,s1.stor_id,s1.qtyfrom (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
Результат:
rank     stor_id  qty-------  -------  ------1        7131     1302        7066     1253        7067     904        8042     805        7896     606        6380     8(6 row(s) affected)
Примечание Используя ранжирующие функции, в SQL Server 2005 можно правильно ранжировать магазины и получить правильную информацию о количестве проданных книг.

К началу статьи

Пример 3

Сведения о примере.
  • В данном примере выполняется ранжирование издателей по их прибыли.
  • Множество 1 представляет общий объем продаж продукции издателя:
            select t.pub_id, sales=sum(s.qty*t.price)        from sales s, titles t        where s.title_id=t.title_id          and t.price is not null        group by t.pub_id					
  • Множество 2 представляет общий объем продаж продукции издателя:
            select t.pub_id, sales=sum(s.qty*t.price)        from sales s, titles t        where s.title_id=t.title_id          and t.price is not null        group by t.pub_id					
  • Отношение формулируется как «зарабатывает больше денег, чем».
Запрос:
   select rank=count(*), s1.pub_id, sales=sum(s1.sales)   from    (select t.pub_id, sales=sum(s.qty*t.price)           from sales s, titles t           where s.title_id=t.title_id             and t.price is not null           group by t.pub_id) s1,           (select t.pub_id, sales=sum(s.qty*t.price)           from sales s, titles t           where s.title_id=t.title_id             and t.price is not null           group by t.pub_id) s2   where s1.sales>= s2.sales   group by s1.pub_id   order by rank				
Результат:
   Rank     Pub_Id   Sales   ----     ------   --------   1         0736    1,961.85   2         0877    4,256.20   3         1389    7,760.85   (3 row(s) affected)				
Примечание. Значения в столбце Sales неверны, однако ранжирование издателей по их прибыли выполняется правильно.

При работе с SQL Server 2005 используйте следующий код.
select rank() over (order by sales desc) as rank,s1.pub_id,s1.sales  from (select t.pub_id, sales=sum(s.qty*t.price)     from sales s inner join titles t     on s.title_id=t.title_id     where  t.price is not null     group by t.pub_id) as s1
Результат:
rank     pub_id  sales-------  ------  ---------1        1389    2586.952        0877    2128.103        0736    1961.85(3 row(s) affected)				
Примечание. Используя ранжирующие функции, можно правильно выполнить ранжирование и получить правильную информацию о прибыли.

К началу статьи

Недостатки

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

    Примечание. При работе с SQL Server 2005 можно использовать функцию row_number() для получения порядкового номера строки независимо от наличия повторяющихся строк.
Пример.
   select rank=count(*), s1.title_id, qty=sum(s1.qty)   from (select title_id, qty=sum(qty) from sales group by title_id) s1,        (select title_id, qty=sum(qty) from sales group by title_id) s2   where s1.qty >= s2.qty   group by s1.title_id   order by rank
Результат:
   Rank    Title_Id    Qty   ----    --------    ----   1       MC2222        10   4       BU1032        60   4       BU7832        60   4       PS3333        60   7       PS1372       140   7       TC4203       140   7       TC7777       140   10      BU1111       250   10      PS2106       250   10      PS7777       250   11      PC1035       330   12      BU2075       420   14      MC3021       560   14      TC3218       560   15      PC8888       750   16      PS2091      1728   (16 row(s) affected)				
К началу статьи

Преимущества

  • Приведенные запросы можно использовать в представлениях и при форматировании результатов.
  • Данные с меньшим рангом можно сдвинуть вправо.
Пример 1.
   CREATE VIEW v_pub_rank   AS   select rank=count(*), s1.title_id, qty=sum(s1.qty)   from (select title_id, qty=sum(qty) from sales group by title_id) s1,        (select title_id, qty=sum(qty) from sales group by title_id) s2   where s1.qty >= s2.qty   group by s1.title_id				
Запрос:
   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +           pub_id +           replicate(' ', 15-power(2,rank))+': '),           earnings=qty   from v_pub_rank				
Результат:
   Publisher       Earnings   -------------   --------     0736          : 1,961.85       0877        : 4,256.20           1389    : 7,760.85				
При работе с SQL Server 2005 используйте следующий код.
CREATE VIEW v_pub_rankASselect rank() over (order by sales) as rank,s1.pub_id,s1.sales  	from (select t.pub_id, sales=sum(s.qty*t.price)	from sales s, titles t	where s.title_id=t.title_id	and t.price is not null	group by t.pub_id) as s1GOselect  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +	pub_id + replicate(' ', 15-power(2,rank))+': '),	earnings=salesfrom v_pub_rank order by rankGO
Результат:
publisher            earnings-------------------- ---------------------  0736             : 1961.85    0877           : 2128.10        1389       : 2586.95(3 row(s) affected)

Пример 2.
   CREATE VIEW v_title_rank   AS   select rank=count(*), s1.title_id, qty=sum(s1.qty)   from (select title_id, qty=sum(qty) from sales group by title_id) s1,        (select title_id, qty=sum(qty) from sales group by title_id) s2   where s1.qty >= s2.qty   group by s1.title_id				
Запрос:
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +           title_id +           replicate(' ', 35-2*rank)+': '),           qty   from v_title_rank   order by rank				
Результат:
   Book                                          Qty   -------------------------------------------   ----     MC2222                                 :      10           BU1032                           :      60           BU7832                           :      60           PS3333                           :      60                 PS1372                     :     140                 TC4203                     :     140                 TC7777                     :     140                       BU1111               :     250                       PS2106               :     250                       PS7777               :     250                         PC1035             :     330                           BU2075           :     420                               MC3021       :     560                               TC3218       :     560                                 PC8888     :     750                                   PS2091   :    1728   (16 row(s) affected)				
При работе с SQL Server 2005 используйте следующий код.
CREATE VIEW v_title_rankASselect rank() over (order by qty) as rank, s1.title_id,s1.qtyfrom (select title_id, qty=sum(qty) from sales group by title_id) as s1GOselect Book=convert(varchar(45),replicate (' ', 2*rank) +title_id + replicate(' ', 35-2*rank)+': '), qtyfrom v_title_rankorder by rankGO
Результат:
Book                                          qty--------------------------------------------- -----------  MC2222                                 :    10    BU1032                               :    15    BU7832                               :    15    PS3333                               :    15          TC4203                         :    20          TC7777                         :    20          PS1372                         :    20                BU1111                   :    25                PS7777                   :    25                PS2106                   :    25                      PC1035             :    30                        BU2075           :    35                          MC3021         :    40                          TC3218         :    40                              PC8888     :    50                                PS2091   :    108(16 row(s) affected) 
К началу статьи
prodsql join rank formatting query
Свойства

Номер статьи: 186133 — последний просмотр: 01/30/2014 11:15:00 — редакция: 7.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 4.21a Standard Edition, Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsqlsetup kbhowtomaster KB186133
Отзывы и предложения