Отправляет 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
31


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


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

Автор
Сергей

Статистика

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

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


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

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

Выпуск No. 4 от 2003-03-18
Вопрос : Что такое подзапросы (вложенные запросы, подвыборки), и где они используются?
Ответ:
Подзапрос - это оператор SELECT, заключенный в круглые скобки и встроенный в тело другого запроса.
Подзапрос может использоваться в любых частях основного запроса.
Подзапросы также могут содержать подзапросы - под-подзапросы.
Подзапрос может выполняться либо один раз, вместе с выполнением основного запроса, либо для каждой записи основного запроса в отдельности. Выполнение подзапроса для каждой записи в отдельности существенно влияет на скорость выполнения запроса. Хотя план выполнения подзапроса не меняется в процессе выполнения основного запроса, тем не менее, следует избегать использования таких подзапросов для большого количества записей.

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

(

ClientID int,

Date datetime,

Amount money

  CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED

  (ClientID, Date)

)

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

1. Подзапрос как колонка в основном запросе
Создадим запрос, в котором наряду с информацией по продаже будет колонка, отображающая сумму продаж по каждому клиенту нарастающим итогом: 
SELECT a.*,

  (

    select sum(Amount)

      from Sales b

      where b.ClientID=a.ClientID and b.Date<=a.Date

  ) AS SumAmount

  FROM Sales a

GO

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

----------- ------------ --------------------- ---------------------

1           01.03.2003   15.4800               15.4800

1           02.03.2003   134.0100              149.4900

1           03.03.2003   2346.0300             2495.5200

2           03.03.2003   754.8800              754.8800

3           01.03.2003   73.0700               73.0700

3           02.03.2003   734.4600              807.5300

4           01.03.2003   1567.1000             1567.1000

4           04.03.2003   6575.7000             8142.8000

4           07.03.2003   6575.7700             14718.5700

4           09.03.2003   6575.3700             21293.9400

5           01.03.2003   1975.7300             1975.7300

5           06.03.2003   178965.6300           180941.3600

6           03.03.2003   16785.3400            16785.3400

6           04.03.2003   1705.4400             18490.7800



(14 row(s) affected)

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

2.Процент текущей продажи от общего объема продаж по клиенту
Создадим запрос, в котором для каждой продажи вычислим ее процент от общего объема продаж по клиенту. Чтобы это сделать необходимо получить сумму продаж по каждому клиенту: 
SELECT a.*,

  b.Amount AS SumAmount,

  (a.Amount*100/b.Amount) AS SalePercent

  FROM Sales a

  JOIN (

    select ClientID, sum(Amount) as Amount

      from Sales

      group by ClientID

  ) b ON b.ClientID=a.ClientID

GO

Результат: 
ClientID Date         Amount        SumAmount     SalePercent

-------- ------------ ------------- ------------- -----------

1        01.03.2003   15.4800       2495.5200     .6203

1        02.03.2003   134.0100      2495.5200     5.3700

1        03.03.2003   2346.0300     2495.5200     94.0096

2        03.03.2003   754.8800      754.8800      100.0000

3        01.03.2003   73.0700       807.5300      9.0485

3        02.03.2003   734.4600      807.5300      90.9514

4        01.03.2003   1567.1000     21293.9400    7.3593

4        04.03.2003   6575.7000     21293.9400    30.8806

4        07.03.2003   6575.7700     21293.9400    30.8809

4        09.03.2003   6575.3700     21293.9400    30.8790

5        01.03.2003   1975.7300     180941.3600   1.0919

5        06.03.2003   178965.6300   180941.3600   98.9080

6        03.03.2003   16785.3400    18490.7800    90.7768

6        04.03.2003   1705.4400     18490.7800    9.2231



(14 row(s) affected)

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

3. Последние две продажи по каждому клиенту
Попробуем получить запрос, в котором будут показаны только две последние продажи по каждому клиенту: 
SELECT a.*

  FROM Sales a

  WHERE a.Date IN

  (select top 2 Date

    from Sales b

    where b.ClientID=a.ClientID

    order by Date desc)

GO

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

----------- ------------ ---------------------

1           02.03.2003   134.0100

1           03.03.2003   2346.0300

2           03.03.2003   754.8800

3           01.03.2003   73.0700

3           02.03.2003   734.4600

4           07.03.2003   6575.7700

4           09.03.2003   6575.3700

5           01.03.2003   1975.7300

5           06.03.2003   178965.6300

6           03.03.2003   16785.3400

6           04.03.2003   1705.4400



(11 row(s) affected)

В этом случае подзапрос опять выполняется для каждой записи основного запроса и возвращает даты двух последних продаж по текущему клиенту. Подзапрос используется в разделе WHERE.
Примечание: Эта выборка будет работать только в 2000-й версии SQL Server из-за использования оператора TOP.

Добавлю, что при работе в SQL Server 7.0 без установленных "сервиспаков" некоторые виды выборок с подзапросами выдают неверные результаты.

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

GO
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес 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 		Отписаться
Убрать рекламу
В избранное