Перекрестный запрос sql: Перекрестные запросы SQL или кросс табличные выражения | Info-Comp.ru

Перекрестные запросы SQL или кросс табличные выражения | Info-Comp.ru

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

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

• Основы языка запросов SQL – оператор SELECT;
• Строковые функции SQL – примеры использования.

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

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

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

Пример перекрестного запроса с использованием 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

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

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

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

Пример перекрестного запроса с использованием оператора 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 в следующих материалах.

SQL.RU | Перекрестные запросы в Т-SQL

Перекрестные запросы (Crosstab Query) являются еще одной специфической разновидностью запросов на выборку. Предназначены они для более глубокого анализа информации, хранящейся в таблицах.

Ключевым словом SQL-оператора перекрестного запроса, задающим его тип, является слово TRANSFORM (преобразовать). Это подразумевает, что значения одного из столбцов (полей) выборки, будут преобразованы в названия столбцов итоговой выборки.

Результаты перекрестного запроса группируются по двум наборам данных, один из которых расположен в левом столбце (столбцах) таблицы, а второй — в верхней строке. В остальном пространстве таблицы отображаются результаты статистических расчетов (Sum, Count и т.д.), выполненных над данными трансформированного поля.

Для демонстрации создадим тестовую таблицу:

CREATE TABLE Sales
(
SaleID int IDENTITY PRIMARY KEY CLUSTERED,
ClientID int,
Date datetime,
Amount money
)
insert Sales values(1,'20010401', 15.48)
insert Sales values(1,'20020302', 134.01)
insert Sales values(1,'20031003', 2346.03)
insert Sales values(2,'20030203', 754.88)
insert Sales values(3,'20010301', 73.07)
insert Sales values(3,'20030402', 734.46)
insert Sales values(4,'20010301', 1567.10)
insert Sales values(4,'20020404', 6575.70)
insert Sales values(4,'20030307', 6575.77)
insert Sales values(4,'20030309', 6575.37)
insert Sales values(5,'20011201', 1975.73)
insert Sales values(5,'20030306', 178965.63)
insert Sales values(6,'20020103', 16785.34)
insert Sales values(6,'20030304', 1705. 44)
GO

До версии SQL Server 2005, в котором появился оператор

SELECT
  MONTH(Date) AS SaleMonth,
  SUM(CASE YEAR(Date)
        WHEN 2001 THEN Amount
        ELSE 0
      END) AS [2001],
  SUM(CASE YEAR(Date)
        WHEN 2002 THEN Amount
        ELSE 0
      END) AS [2002],
  SUM(CASE YEAR(Date)
        WHEN 2003 THEN Amount
        ELSE 0
      END) AS [2003]
FROM Sales
GROUP BY MONTH(Date)
ORDER BY MONTH(Date)
GO

Результат выполнения запросa:

SaleMonth   2001                  2002                  2003                 
———— ——————— ——————— ———————
1           .0000                 16785.3400            .0000
2           .0000                 .0000                 754.

(6 row(s) affected)

Теперь тот же запрос, но уже с использованием оператора PIVOT (версия SQL Server 2005 и выше):

SELECT * FROM
(SELECT YEAR(Date) y, MONTH(Date) SaleMonth, Amount FROM Sales) as s
PIVOT
(SUM(Amount) for y in ([2001], [2002], [2003])) pv

Всё бы хорошо, когда мы за ранее знаем кол-во лет, но что делать, если мы не имеем такой информации на входе, либо нужно, чтобы код был более универсальным и при добавлении записей с более старшей датой код был «рабочим». Выход-динамический перекрестный запрос (Dynamic Crosstab Queries). Усложним наши тестовые данные добавив хотя бы одну запись с другим годом:

insert Sales values(1,'20010401', 15.48)

Очень интересное решение этой задачи предложил Ицик Бен-Ган (http://am. rusimport.ru/MsAccess/topic.aspx?id=285):

CREATE PROCEDURE sp_CrossTab
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- Шаг 1: начало строки SQL.
SET @sql =
  'SELECT' + @NEWLINE +
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END

-- Шаг 2: Хранение ключей во временной таблице.
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table

EXEC (@keyssql)

-- Шаг 3: Средняя часть строки SQL.
  
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS [' + @key+']'

  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

-- Шаг 4: Конец строки SQL.
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

SET NOCOUNT OFF
PRINT @sql  + @NEWLINE-- для отладки
EXEC (@sql)
GO

Вызов этой процедуры:

EXEC sp_CrossTab
  @table       = 'Sales',
  @onrows      = 'MONTH(Date)',
  @onrowsalias = 'SaleMonth',
  @oncols      = 'YEAR(Date)',
  @sumcol      = 'Amount'
GO

 Но хотелось бы немного упростить эту процедуру с помощью оператора PIVOT и вот, что получилось:

CREATE PROCEDURE sp_CrossTab_PIVOT
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS nvarchar (max),
  @case AS varchar(1000)
SET @case=''
SELECT @sql='
SELECT @case=@case+''[''+CONVERT(VARCHAR, '+@oncols+')+''], '''+
' FROM '+@table+' GROUP BY '+@oncols+' ORDER BY '+@oncols
 
EXEC sp_executesql @sql,N'@case varchar(1000) out', @case=@case out
SET @case=LEFT(@case, LEN(@case)-1)

SELECT @sql='SELECT * FROM (
SELECT '+@oncols+' y, '+@onrows+' '+@onrowsalias+', '+@sumcol+' FROM '+@table+
') as s
PIVOT
(SUM ('+@sumcol+') for y in ('+@case+')) as pv'
PRINT @sql -- для отладки 
EXECUTE (@sql)

Вызов моей процедуры идентичен вызову предыдущей процедуры:

EXEC sp_CrossTab_PIVOT
  @table       = 'Sales',
  @onrows      = 'MONTH(Date)',
  @onrowsalias = 'SaleMonth',
  @oncols      = 'YEAR(Date)',
  @sumcol      = 'Amount'
GO

Ещё одно решение этой задачи можно найти в FAQ на сайте SQL. RU (http://www.sql.ru/faq/faq_topic.aspx?fid=358)

SQL CROSS JOIN с примерами

В этой статье мы изучим концепцию SQL CROSS JOIN и подкрепим наши знания простыми примерами, которые объясняются иллюстрациями.

Введение

CROSS JOIN используется для создания парной комбинации каждой строки первой таблицы с каждой строкой второй таблицы. Этот тип соединения также известен как декартово соединение.

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

На следующем изображении показаны все комбинации меню, которые может генерировать наш мозг. SQL CROSS JOIN работает аналогично этому механизму, поскольку создает все парные комбинации строк таблиц, которые будут объединены.

«Пожалуйста, не волнуйтесь, даже если вы чувствуете себя немного голодным сейчас, вы можете есть все, что захотите, после прочтения нашей статьи».

Основная идея CROSS JOIN заключается в том, что она возвращает декартово произведение соединенных таблиц. В следующем совете мы кратко объясним декартово произведение;

Совет: Что такое декартово произведение?

Декартово произведение — это операция умножения в теории множеств, порождающая все упорядоченные пары заданных множеств. Предположим, что A — это множество, а элементами являются {a,b}, а B — это множество, а элементами являются {1,2,3}. Декартово произведение этих двух A и B обозначается AxB, и результат будет примерно следующим.

AxB = {(а, 1), (а, 2), (а, 3), (б, 1), (б, 2), (б, 3)}

Синтаксис

Синтаксис CROSS JOIN в SQL будет выглядеть следующим образом:

 Дата Teamname Teammemebername
 

 ID ScheduleDate TeamCode TeamMemberCode
5585 04. 03.2022 NT СЗ
5586 04.03.2022 УГРЛ РН
5587 04.03.2022 УШРТ КН
5588 04.03.2022 УШРТ КИ
5589 04.03.2022 УШРТ РФ
5590 11.03.2022 НТ МФ
5591 11.03.2022 USHRL QD
5592 11.03.2022 УШРТ СЗ
5593 11.03.2022 УШРТ КН
5594 11.03.2022 УШРТ КИ
 

 Имена функций (дата как вариант, команда как вариант)
 Dim res$, sql$
 Dim rs As DAO.Recordset
 Если IsNull(дата) или IsNull(команда) Тогда
 имена = ноль
 Еще
 sql = "ВЫБЕРИТЕ * ИЗ Teamdata"
 sql = sql & " Где ScheduleDate = # " & dat & "#"
 sql = sql & " AND TeamCode=""" & team & """"
 sql = sql & " Заказ по TeamMemberCode;"
 Установить rs = CurrentDb.
Добавить комментарий Отменить ответ
Ваш адрес email не будет опубликован. Обязательные поля помечены *
Комментарий *
Имя * 
Email * 
Сайт