Перекрестные 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
Существует несколько способов реализовать перекрестный запрос. Например, следующий, он подойдет практически для любой версии СУБД. Для нашей с Вами тестовой задачи запрос будет выглядеть так:
В результате вы получите следующий вывод данных:
tovar |
2009 |
2010 |
2011 |
Сканер |
5 |
8 |
4 |
Монитор |
5 |
4 |
6 |
Принтер |
5 |
3 |
|
Сравните исходную нашу таблицу и результат этого запроса, по моему сейчас более наглядно видно, сколько продаж в каком году было сделано.
Примечание! Сразу скажу, что все перечисленные в данной статье запросы вы сможете применить, только если Вы заранее знаете, сколько у Вас будет выводиться столбцов (у нас это «года» и их всего 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Если Вы работаете в Access, то там вообще все просто, это можно реализовать стандартными средствами Access, даже если Вы не знаете SQL. Просто нажмите «Создать новый запрос» и выберете «Перекрестный запрос». После создания запроса можете открыть запрос в режиме конструктора и перейти в режим SQL, где Вы сможете увидеть сам запрос на SQL.
На сегодня о перекрестных запросах я думаю достаточно. Продолжим изучение SQL в следующих материалах.
Похожие статьи:
info-comp.ru
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Результат выполнения запрос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Но хотелось бы немного упростить эту процедуру с помощью оператора 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)Ещё одно решение этой задачи можно найти в 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
Перекрестные 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
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 + "GROUP BY " + @onrows + @NEWLINE + "ORDER BY " + @onrows
--PRINT @sql + @NEWLINE-- EXEC (@sql) GO
Вызываешь ее таким образом: sp_CrossTab @table = "MyTable", @onrows = "MyFldRow" @oncols = "MyCols", @sumcol = "RESULT"
Где MyTable - таблица с которой берешь данные MyFldRow - Колонки, данные которых будут строками (через запятую) MyCols - Имя колонки данные которой будут колонками RESULT Имя колонки данные которой будут на пересечении (Здесь SUM если разберешься можно max,min и т.д)
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