Перекрестные SQL запросы в СУБД Access. Sql перекрестный запрос пример

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

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

Для начала напомню, что это у нас уже третья статья по 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 в следующих материалах.

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, в котором появился оператор PIVOT, перекрестные запросы выполнялись через оператор CASE (BOL->Cross-Tab Reports):

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 .00002 .0000 .0000 754.88003 1640.1700 134.0100 193822.21004 15.4800 6575.7000 734.460010 .0000 .0000 2346.030012 1975.7300 .0000 .0000

(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)

www.t-sql.ru

2.9. Перекрестные запросы на sql.

Инструкция TRANSFORM позволяет создавать перекрестные запросы, позволяющие осуществлять суммирование некоторого выражения; используя значение заданного столбца или выражения в качестве заголовков столбцов выходных таблиц, а другие столбцы либо выражения используются для задания условия группировки и формирования строк выходной таблицы.

Синтаксис инструкции TRANSFORM:

TRANSFORM выражение_с_итоговой_функцией

<инструкция_SELECT>

PIVOT выражение

где <выражение_с_итоговой_функцией> − выражение, использующее одну из итоговых функций. Оно определяет значения, которые должны появиться в ячейках выходной (перекрестной) таблицы.

Выражение <инструкция_SELECT> должна содержать предложение GROUP BY.

Конструкция <PIVOT выражение> определяет столбец или выражение, значения которого используются в качестве заголовков столбцов перекрестной таблицы.

При задании условий группировки для строк можно использовать несколько

столбцов или выражений.

Чтобы получить итоговые оценки по предметам, создадим запрос "Сетка успеваемости":

TRANSFORM MAX(INT((Результаты.Оценка_Т+Результаты.Оценка_П+ Результаты.Оценка_Л)/З))

SELECT Результаты.Номер_С

FROM Результаты

GROUP BY Результаты.Номер_С

PIVOT Предмет;

Запросы можно строить не только на основе данных таблиц, но и по данным, которые возвращает запрос. В этом случае имя вызываемого запроса указывается в квадратных скобках. Так, при необходимости сформировать сетку успеваемости за учебный год по прошедшему и текущему семестрах, SQL не дает возможности напрямую произвести объединение таблиц в перекрестном запросе. Приходится создавать запрос на объединение таблиц − "Объединенные результаты", а затем создаваемый перекрестный запрос "Сетка успеваемости за год", который использует результаты уже запроса-объединения:

TRANSFORM MAX(INT((Результаты.Оценка_Т+Результаты.Оценка_П+ Результаты.Оценка_Л)/))

SELECT [Объединенные результаты].Номер_С

FROM [Объединенные Результаты]

GROUP BY [Объединенные результаты].Номер_С

PIVOT Предмет;

2.10. Запросы − действия на sql.

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

2.10.1. Запрос на создание таблицы.

Инструкция SELECT...INTO создает новую таблицу, используя значения, выбранные из одной или нескольких таблиц. Обычно этот тип запросов применяется при резервировании данных таблицы, или для вывода отчетов за определенный период.

Синтаксис команды:

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число [PERCENT]] список_выбора

INTO имя_новой_таблицы

FROM {{имя_таблицы [[AS] псевдоним] |

имя_запроса_выборки [[AS] псевдоним]} |

<таблица_объединение> }

[WHERE условие_отбора]

[GROUP BY имя_столбца ,...]

[HAVING условие_отбора]

[UNION [ALL] инструкция_выбора]

[ORDER BY { имя_столбца [ ASC | DESC ] },...

IN<"имя_базы_источника_данных"> <[строка_подключения_источника_дан-ных]>

Модификация вставляемых данных возможна только в режиме

SELECT DISTINCTROW.

Данный запрос создает новую таблицу с именем, задаваемым в <имя_новой_таблицы>. Если таблица с таким именем уже существует ACCESS запрашивает, удалять ли старую перед созданием новой.

Создадим запрос "Результат по практике".

SELECT DISTINCTROW Фамилия, Имя, Отчество, Студенты.Номер_С,

Count(Результат.Оценка_П) AS Практика

FROM Студенты

INNER JOIN Результаты

ON Студенты.Номер_С = Результаты.Номер_С

WHERE ((Результаты.Предмет=[Введите предмет])

GROUP BY Фамилия, Имя, Отчество, Студенты.Номер_С;

После этого создадим запрос, который будет по полученной выборке формировать таблицу "Результат":

SELECT DISTINCTROW [Результат по практике].*

INTO [Результат]

FROM [Результат по практике];

Запустим запрос на выполнение и после введения всех параметров убедимся, что Access создал таблицу "Результат".

studfiles.net

Перекрестные SQL запросы в СУБД Access 2003 и 2007

2016-06-25

Автор: Владимир Ткаченко

Источник: Обучение в интернет

В данной статье рассмотрим перекрестные SQL запросы на выборку данных из таблиц БД Access. Для создания перекрестного SQL запроса откроем базу данных sql_training_st1_param.mdb.

Перекрестные запросы применяются в том случае если нужно упорядочить информацию из базы данных по двум или более параметрам. Cоздать перекрестный запрос в СУБД Access 2003 - 2007 можно используя, Мастер создания перекрестных запросов, конструктор запросов и режим SQL. Например, в результате выполнения обычного запроса на выборку получим несгруппированные по фамилиям и дисциплинам данные (рис.1).

Рис. 1. Результат выполнения обычного запроса на выборку данных

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

Рис. 2. Результат выполнения перекрестного запроса на выборку данных

Как следует из таблиц рис. 1 и 2, в таблице рис. 2 представлена сгруппированная информация, т.е. представлена более упорядоченная информация по сравнению с представленной информацией в таблице рис. 1.

Для создания перекрестного SQL запроса или инструкции SQL (рис. 2) необходимо в открытой БД sql_training_st1_param.mdb на вкладке "Создание" выбрать команду "Конструктор запросов". Откроется активное окно диалога "Добавление таблицы" на фоне неактивного окна «Запрос1», далее надо закрыть окно диалога "Добавление таблицы". Затем на контекстной вкладке "Конструктор" выбрать режим SQL, выполнив команду SQL, в результате в окне редактирования будет отображаться оператор SELECT.

Удалим оператор SELECT, и введем с клавиатуры следующую инструкцию SQL:TRANSFORM Min(Успеваемость.Оценка) AS [Min-Оценка]SELECT Студенты.Фамилия, Студенты.ИмяFROM Студенты INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины = Успеваемость.КодДисциплины) ON Студенты.КодСтудента = Успеваемость.КодСтудентаGROUP BY Студенты.Фамилия, Студенты.ИмяPIVOT Дисциплины.Название;

Рис. 3. Перекрестный SQL запрос на выборку данных

Эта инструкция состоит из пяти предложений "TRANSFORM...", "SELECT. . .", "FROM. . .", "GROUP BY..." и "PIVOT...". Первое предложение содержит оператор TRANSFORM, который является необязательным. Но если его включить, то его надо включить первым оператором в инструкцию SQL. За этим оператором была включена агрегатная функция "Min(Успеваемость.Оценка) AS [Min-Оценка]".

Второе предложение содержит оператор SELECT и идентификатор "Студенты.Фамилия, Студенты.Имя". Выбор данных осуществляется из двух полей "Фамилия" и "Имя" одной таблицы "Студенты". Оператор SELECT указывает поля, которые используются как заголовки строк в таблице результатов перекрестного запроса.

Третье предложение содержит оператор FROM и идентификатор "Студенты INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины = Успеваемость.КодДисциплины) ON Студенты.КодСтудента = Успеваемость.КодСтудента". FROM - определяет таблицы "Студенты", "Дисциплины" и "Успеваемость", которые содержат поля, и обеспечивает взаимосвязи таблиц через ключевые поля таблиц с помощью конструкции INNER JOIN ... ON.

Оператор GROUP BY используется для определения групп "Студенты.Фамилия, Студенты.Имя", к которым применяется агрегатная функция Min.

В операторе PIVOT перечислены столбцы для вывода информации о дисциплинах.

В результате выполнения команды "Сохранить" в «Области переходов» появится объект - «Запросы: cros-tab_sql». После сохранения перекрестного SQL запроса на выборку данных необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить» (рис.3).

Результаты выполнения команды «Выполнить» представлены на рис. 4.

Рис. 4. Результаты выполнения перекрестного SQL запроса на выборку данных

Сохраним базу данных sql_training_st1_param.mdb с созданным перекрестным SQL запросом на выборку данных (cros-tab_sql) под именем sql_training_st1_cross-tab.mdb.

Скачать sql_training_st1_cross-tab.mdb

www.lessons-tva.info

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

Форум: "Базы";Поиск по всему сайту: www.delphimaster.net;Текущий архив: 2003.06.02;Скачать: [xml.tar.bz2];

Вниз

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

mcmax (2003-05-14 16:08) [0]

Подскажите плз, как можно ли в Oracle выполнить перекрестный запрос с группировкой? В Jet SQL это делается при помощи ключевых слов Transform и Pivot, а вот как в Oracle - не знаю?

Stas (2003-05-14 16:13) [1]

Есть х.п. под msSQl если разберешься переделаешь на свой сервак.

mcmax (2003-05-14 16:40) [2]

Не совсем понял что есть х.п. ? Если пример то кидай, попробую разобраться...

Stas (2003-05-14 16:49) [3]

х.п.-Хранимая процедура (Stored Procedure).

CREATE PROC sp_CrossTabDp @table AS sysname, @onrows AS nvarchar(128), @onrowsalias AS sysname = NULL, @oncols AS nvarchar(256), @sumcol AS sysname = NULL , @dated as nvarchar(10), @dateu as nvarchar(10) AS

DECLARE @sql AS varchar(8000), @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

SET @sql = "SELECT" + @NEWLINE + " " + @onrows + CASE WHEN @onrowsalias IS NOT NULL THEN " AS " + @onrowsalias ELSE "" END 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)

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 " + @sumcol+ @NEWLINE + " ELSE NULL" + @NEWLINE + " END) AS " + @key

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

SET @sql = @sql + @NEWLINE + "FROM " + @table + @NEWLINE + "WHERE "+ @sumcol+">="+@DATED+" AND "+@sumcol+"<="+@dateu +@NEWLINE+ "GROUP BY " + @onrows + @NEWLINE + "ORDER BY " + @onrows

--PRINT @sql + @NEWLINE-- EXEC (@sql) GO

Вызываешь ее таким образом: sp_CrossTabDp @table = "MyTable", @onrows = "MyFldRow" @oncols = "MyCols", @sumcol = "RESULT"

Где MyTable - таблица с которой берешь данные MyFldRow - Колонки, данные которых будут строками (через запятую) MyCols - Имя колонки данные которой будут колонками RESULT Имя колонки данные которой будут на пересечении (Здесь SUM если разберешься можно max,min и т.д)

Stas (2003-05-14 16:53) [4]

Извеняюсь!!(Предидущая не верная)

Вот Так

CREATE PROC sp_CrossTab @table AS sysname, @onrows AS nvarchar(128), @onrowsalias AS sysname = NULL, @oncols AS nvarchar(256), @sumcol AS sysname = NULL

DECLARE @sql AS varchar(8000), @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

SET @sql = "SELECT" + @NEWLINE + " " + @onrows + CASE WHEN @onrowsalias IS NOT NULL THEN " AS " + @onrowsalias ELSE "" END 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)

DECLARE @key AS nvarchar(100) SELECT @key = MIN(keyvalue) FROM #keys

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

SET @sql = @sql + @NEWLINE + "FROM " + @table + @NEWLINE + "GROUP BY " + @onrows + @NEWLINE + "ORDER BY " + @onrows

--PRINT @sql + @NEWLINE-- EXEC (@sql) GO

Вызываешь ее таким образом: sp_CrossTab @table = "MyTable", @onrows = "MyFldRow" @oncols = "MyCols", @sumcol = "RESULT"

mcmax (2003-05-14 16:55) [5]

Выглядит страшно :)) Спасибо, пошел вникать...

mcmax (2003-05-15 11:41) [6]

не совсем понятен следующий кусок кода

SET @sql = @sql + "," + @NEWLINE + " SUM(CASE CAST(" + @oncols + " AS NVARCHAR(100))" + @NEWLINE + " WHEN N""" + @key + """ THEN " + @sumcol+ @NEWLINE + " ELSE NULL" + @NEWLINE + " END) AS " + @key

Особенное смущение вызывают оператор Case, When N(что есть N?)? Вобщем если можешь закоментируй этот кусок, что б до меня дошла логика или кинь пример уже сформированной строки @sql

Заранее спасибо

mcmax (2003-05-15 15:27) [7]

Спасибо уже разобрался...

Форум: "Базы";Поиск по всему сайту: www.delphimaster.net;Текущий архив: 2003.06.02;Скачать: [xml.tar.bz2];

Наверх

Память: 0.74 MBВремя: 0.029 c

www.delphimaster.net

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

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

Запросы на изменение

Запросом на изменение называют запрос, который за одну операцию изменяет или перемещает несколько записей. Существует четыре типа запросов на изменение:

на удаление записи. Запрос на удаление удаляет группу записей из одной или нескольких таблиц. Например, запрос на удаление позволяет удалить записи о товарах, поставки которых прекращены или на которые нет заказов. С помощью запроса на удаление можно удалять только всю запись, а не отдельные поля внутри нее;
на обновление записи. Запрос на обновление вносит общие изменения в группу записей одной или нескольких таблиц. Например, на 10 процентов поднимаются цены на все молочные продукты или на 5 процентов увеличивается зарплата сотрудников определенной категории. Запрос на обновление записей позволяет изменять данные в существующих таблицах;
на добавление записей. Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу «Клиенты»;
на создание таблицы. Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных Microsoft Access или при создании архивной таблицы, содержащей старые записи.

Запросы sql

Запрос SQL — это запрос, создаваемый при помощи инструкций SQL. Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access.

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

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

Формы: удобное представление данных

Формы являются типом объектов базы данных, который обычно используется для отображения данных в базе данных. Форму можно также использовать как кнопочную форму, открывающую другие формы или отчеты базы данных, а также как пользовательское диалоговое окно для ввода данных и выполнения действий, определяемых введенными данными.

Большинство форм являются присоединенными к одной или нескольким таблицам и запросам из базы данных. Источником записей формы являются поля в базовых таблицах и запросах. Форма не должна включать все поля из каждой таблицы или запроса, на основе которых она создается.

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

Графические элементы, такие как линии и прямоугольники, сохраняются в макете формы.

Данные поступают из полей в базовом источнике записей.

Результаты расчетов поступают из выражений, которые сохраняются в макете формы.

Поясняющий текст сохраняется в макете формы.

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

В надписях выводится поясняющий текст.

В полях отображаются данные из таблицы и вводятся данные в таблицу.

Поле использует выражение для расчета итогового значения.

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

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

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

studfiles.net

Перекрестный запрос. Access 2002: Самоучитель

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

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

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

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

Задача, для решения которой потребуется перекрестный запрос, формулируется так: необходимо выяснить, сколько аварий (чрезвычайных ситуаций) определенных видов произошло на территории разных субъектов Российской Федерации (краев и областей). Источником информации послужит таблица Fiie1.

Формирование перекрестного запроса лучше всего начать с помощью мастера запросов. Открыв вкладку Запросы окна базы данных, с помощью кнопки

выйдите в окно Новый запрос и в нем выберите опцию Мастер перекрестных запросов (рис. 11.64). На этом этапе надо выбрать запрос, который содержит поля, используемые в перекрестном запросе. В нашем случае следует выбрать таблицу Fiie1. Если в какой-либо ситуации одной таблицы будет мало, то сформируйте запрос, содержащий все нужные вам поля, а затем используйте его как основу для создания перекрестного запроса.

Рис. 11.64

Когда вы сделаете выбор, на экране появится следующее окно мастера перекрестных запросов (см. рис. 11.65), где вам следует решить, значения каких полей вы хотите оформить в виде заголовков строк. Всего можно указать не более трех полей. Для нашей конкретной задачи в качестве заголовков строк послужат значения поля Регион. Щелкните по кнопке Далее, а затем укажите поля (не больше трех), значения которых станут заголовками столбцов. В данном случае будет выбрано поле Наименование ЧС. После этого вы окажетесь в следующем окне мастера (см. рис. 11.66), где предстоит определить, что же вы хотите вычислить для каждой ячейки, расположенной на пересечении столбца и строки. Вы уже использовали два поля: Название области – для заголовков строк, а Наименование ЧС – для заголовков столбцов.

Рис. 11.65

Рис. 11.66

Теперь понадобится третья переменная, значения которой можно подсчитывать, причем они должны соответствовать значениям первых двух полей. Главное, чтобы это третье указанное поле не имело пропусков, то есть нулевых значений. Следовательно, лучше всего подойдет поле Номер. В качестве функции для подсчета числа значений выберите Число (одна из функций групповой обработки данных). Иначе говоря, в конце концов будет определено количество значений поля Номер для каждой ячейки, которая находится на пересечении столбца (ЧС определенного вида) и строки (того или иного региона Российской Федерации) – рис. 11.66. Теперь, когда вы подготовили все необходимое для создания запроса, щелкните по кнопке Далее.

В результате на экране появится окно (см. рис. 11.67), где в формируемый запрос нужно внести последние уточнения. Во-первых, его надо назвать. В принципе это ваше дело, но мы бы посоветовали принять имя, которое по умолчанию предлагает Access 2002: File1-Перекрестный. Затем надо определить, чего вы хотите: выполнить запрос или изменить его оформление. Выберите соответствующую позицию переключателя. Если вы собираетесь выполнить запрос и дизайн вас не интересует, откажитесь от следующего предложения мастера: вывести инструкцию по работе с запросом. Теперь остается только щелкнуть по кнопке Далее, и запрос будет запущен на выполнение. А вот дальше в нашем размеренном сюжете возникает неожиданная интрига – сообщение В перекрестном запросе слишком много заголовков столбцов – 507. Это означает, что последующее выполнение запроса невозможно (см. рис. 11.68). Озабоченность Access вполне понятна.

Рис. 11.67 Рис. 11.68

Фактически вы объявили заголовками столбцов или полей все наименования ЧС в таблице, кроме их дубликатов, которые система не пропустит (вместе с повторяющимися именами таких заголовков было бы не 507, а значительно больше). Действительно, многовато. Объяснение здесь простое: при формировании перекрестного запроса вы не вводили каких-либо критериев отбора. Впрочем, при использовании мастера перекрестных запросов этого сделать все равно нельзя. Если вы хотите задать такие условия, то нужно сформировать обычный запрос.

Ввод условий отбора записей в конструкторе запросов

В окне базы данных на вкладке Запрос откройте ваш перекрестный запрос в режиме конструктора (см. рис. 11.69). Теперь надо задать критерии отбора записей. Будем считать, что нас по-прежнему интересует количество ЧС по регионам, но только если эти ЧС связаны с пожарами.

Рис. 11.69

Поэтому введите в запрос условие *пожар*, как показано на рис. 11.70. Если вы отдадите команду на выполнение запроса, то в результате получите таблицу, фрагмент которой приведен на рис. 11.71.

Рис. 11.70

Рис. 11.71

Предположим, необходимо видоизменить запрос. Нужна справка о числе ЧС по регионам, но теперь нас интересуют аварии, связанные не только с пожарами, но и со взрывами. Кроме условия *пожар* введите еще один критерий отбора – *взрыв*, используя схему «ИЛИ». Иными словами, вы запрашиваете число аварий по регионам, в которых произошли либо пожары, либо взрывы. Если бы вы применили схему «И», система Access 2002 стала бы отбирать сведения из регионов, где произошли и пожары, и… (а это уже совсем другой разговор). Запрос будет выглядеть так, как показано на рис. 11.72.

Рис. 11.72

Результат этого дополненного запроса вы видите на рис. 11.73, где показан тот же фрагмент таблицы, что и в предыдущем случае. Во-первых, число аварий по регионам возросло (Иркутская, Кемеровская области). Во-вторых, увеличился список регионов, например добавилась Мурманская область, которой раньше не было в этом перечне.

Рис. 11.73

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

Поделитесь на страничке

Следующая глава >

it.wikireading.ru