Перекрестные запросы SQL или кросс табличные выражения

Продолжаем осваивать язык запросов SQL и сегодня мы с Вами займемся изучением так называемых перекрестных запросов или их также называют кросс табличные выражения. Иногда можно встретить такое название как транспонирование таблицы (например, в Excel), но смысл во всех этих названиях один и тот же и сейчас в нем мы будем разбираться.

Перекрестные запросы SQL

Для начала напомню, что это у нас уже третья статья по SQL. В предыдущих статьях мы рассмотрели:

А сегодня, как было уже сказано, мы займемся изучением перекрестных запросов. Для чего же нужны эти перекрестные запросы? Чтобы помочь Вам ответить на этот вопрос, я приведу пример, чтобы Вы могли наглядно увидеть применение этих перекрестных запросов.

Допустим, у Вас есть таблица следующего вида (колонки называются god, chislo, tovar, сама таблица называется tabl).

Год Число продаж Товар
2009 5 Монитор
2010 3 Принтер
2011 4 Сканер
2009 5 Принтер
2010 8 Сканер
2011 6 Монитор
2009 5 Сканер
2010 4 Монитор
2011 7 Принтер

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

Пример перекрестного запроса с использованием CASE WHEN

Существует несколько способов реализовать перекрестный запрос. Например, следующий, он подойдет практически для любой версии СУБД. Для нашей с Вами тестовой задачи запрос будет выглядеть так:

  
  SELECT tovar,
           SUM (CASE god WHEN '2009' THEN chislo ELSE 0 END) AS "2009",
           SUM (CASE god WHEN '2010' THEN chislo ELSE 0 END) AS "2010",
           SUM (CASE god WHEN '2011' THEN chislo ELSE 0 END) AS "2011"
  FROM tabl 
  GROUP BY tovar

В результате вы получите следующий вывод данных:

tovar 2009 2010 2011
Сканер 5 8 4
Монитор 5 4 6
Принтер 5 3 7

Сравните исходную нашу таблицу и результат этого запроса, по моему сейчас более наглядно видно, сколько продаж в каком году было сделано.

Примечание! Сразу скажу, что все перечисленные в данной статье запросы вы сможете применить, только если Вы заранее знаете, сколько у Вас будет выводиться столбцов (у нас это «года» и их всего 3, если было 4, то нам пришлось бы добавлять в запрос еще одну строку и так далее), т.е. только фиксированное количество столбцов, которое Вам заранее известно. А если Вы не знаете, сколько необходимо выводить лет или их количество будет постоянно меняться, Вам уже нужно будет писать динамически расширяемый перекрестный запрос с помощью специальных процедур. В данном уроке такой способ мы рассматривать не будем.

Пример перекрестного запроса с использованием оператора PIVOT

Переходим к следующему способу, который появился с выходом Microsoft SQL Server 2005. В более ранних версиях этот способ применить нельзя, так как там отсутствовал оператор PIVOT. С использованием этого способа запрос будет выглядеть следующим образом, и результат будет тот же самый:

  
  SELECT tovar, [2009], [2010], [2011]
  FROM tabl
  PIVOT(SUM (chislo) FOR god IN ([2009], [2010], [2011])) AS mytabl

Как видите этот запрос уже немного покороче, но у него своеобразный синтаксис (если конкретней, то у оператора PIVOT).

Если Вы работаете в Access, то там вообще все просто, это можно реализовать стандартными средствами Access, даже если Вы не знаете SQL. Просто нажмите «Создать новый запрос» и выберете «Перекрестный запрос». После создания запроса можете открыть запрос в режиме конструктора и перейти в режим SQL, где Вы сможете увидеть сам запрос на SQL.

На сегодня о перекрестных запросах я думаю достаточно. Продолжим изучение SQL в следующих материалах.

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 6
  1. FasT

    Спасибо, я в postgresql как раз первый вариант использую, все отлично работает

  2. Yakov

    Спасибо, все доступно описано!!!)))

  3. GANI

    Спасибо, Отлично написано!

  4. Илья

    на мой взгляд не достаточно подробно описано

  5. Админ (автор)

    Подробней оператор PIVOT мы рассмотрели вот здесь

  6. Grom

    Можно написать функцию для автоматического формирования запроса для первого варианта
    например:
    declare @Sql nvarchar(1000),@k int, @i int,@god nvarchar(4)
    declare cur cursor for
    select god from tabl group by god
    set @k = (select count(*) from tabl group by god)
    open cur
    set @i=1
    set @sql = ‘select tovar,’
    fetch next from cur into @god
    while @@FETCH_Status=0
    begin
    if @i=@k
    set @sql = @sql + ‘sum(case god when ‘+char(39)+@god+char(39)+’ then chislo else 0 end ) as ‘+char(39)+@god+char(39)+’ ‘
    else
    set @sql = @sql + ‘sum(case god when ‘+char(39)+@god+char(39)+’ then chislo else 0 end ) as ‘+char(39)+@god+char(39)+’, ‘
    set @i = @i + 1
    fetch next from cur into @god
    end
    close cur
    deallocate cur
    set @sql = @sql + ‘from tabl group by tovar’
    —тогда не придётся ежегодно переделывать))

Добавить комментарий для Админ Отменить ответ

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.