Tsql теория > Динамическая нумерация строк в запросе SELECT языка Transact-SQL
25.02.2014 10:05:53
Наиболее часто встречающиеся слова в статье:
[title_id] [qty=sum] [stor_id] [Результат] [affected] [replicate] [sales=sum] [Множество] [представляет] [rank=count]
Статья:
Пример 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
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)
При работе с SQL Server 2005 используйте следующий код.
select row_number() over (order by qty desc) as rank,s1.stor_id,s1.qty
from (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
rank stor_id qty ------- ------- ------ 1 7131 130 2 7066 125 3 7067 90 4 8042 80 5 7896 60 6 6380 8 (6 row(s) affected)
Пример 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)
При работе с 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.95 2 0877 2128.10 3 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)
Преимущества
- Приведенные запросы можно использовать в представлениях и при форматировании результатов.
- Данные с меньшим рангом можно сдвинуть вправо.
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
CREATE VIEW v_pub_rank
AS
select 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 s1
GO
select publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
pub_id + replicate(' ', 15-power(2,rank))+': '),
earnings=sales
from v_pub_rank order by rank
GO
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)
CREATE VIEW v_title_rank
AS
select rank() over (order by qty) as rank, s1.title_id,s1.qty
from (select title_id, qty=sum(qty) from sales group by title_id) as s1
GO
select Book=convert(varchar(45),replicate (' ', 2*rank) +
title_id + replicate(' ', 35-2*rank)+': '), qty
from v_title_rank
order by rank
GO
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)