Excel запрос sql

Примечание

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

как передать параметры в запрос в SQL (Excel)

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

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

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

В большинстве онлайн-ответов на этот вопрос, по-видимому, говорится, что вы должны добавить «?» В запрос, чтобы Excel запрашивал у вас настраиваемый параметр, или поместите подсказку или ссылку на ячейку в [круглые скобки] там, где должен быть параметр.Это может работать для запроса ODBC, но, похоже, не работает для OLE DB, возвращая «нет значения для одного или нескольких обязательных параметров» в первом экземпляре и «Недопустимое имя столбца« xxxx »или« Неизвестный объект« xxxx » "в последних двух. Точно так же использование мифических кнопок «параметры…» или «Редактировать запрос…» также невозможно, так как в этом случае они постоянно отображаются серым цветом. (Для справки: я использую Excel 2010, но с книгой Excel 97-2003 (*.xls))

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

Сначала добавьте строку над таблицей внешних данных (или где угодно), где вы можете поместить подсказку параметра рядом с пустой ячейкой и кнопкой (Разработчик->Вставка->Кнопка (Управление формой) - вам может потребоваться включить вкладку Разработчик , но вы можете узнать, как это сделать в другом месте), например:

Затем выберите ячейку в области внешних данных (синяя), затем откройте данные -> обновить все (раскрывающийся список) -> свойства подключения... взглянуть на ваш запрос. Код в следующем разделе предполагает, что у вас уже есть параметр в вашем запросе (свойства соединения-> определение-> текст команды) в форме «ГДЕ (DB_TABLE_NAME.Field_Name = 'параметр запроса по умолчанию')» (включая круглые скобки). Очевидно, что «DB_TABLE_NAME.Field_Name» и «Параметр запроса по умолчанию» должны быть разными в коде, в зависимости от имени таблицы базы данных, имени поля (столбца), значений базы данных и значения по умолчанию для поиска при открытии документа (если вы установлено автоматическое обновление).Запишите значение «DB_TABLE_NAME.Field_name», которое вам понадобится в следующем разделе, вместе с вашим «именем соединения» в верхней части диалогового окна.

Закройте свойства соединения и нажмите Alt + F11, чтобы открыть редактор VBA. Если вы еще не на нем, щелкните правой кнопкой мыши имя листа, содержащего вашу кнопку, в окне «Проект» и выберите «Просмотр кода». Вставьте приведенный ниже код в окно кода (рекомендуется копирование, поскольку одинарные/двойные кавычки рискованны и необходимы).

  Sub RefreshQuery () Dim queryPreText как строка Dim queryPostText как строка Dim valueToFilter As String Dim paramPosition As Integer valueToFilter = "DB_TABLE_NAME.Field_Name =" С ActiveWorkbook.Connections («имя подключения»). queryPreText = .CommandText paramPosition = InStr (queryPreText, valueToFilter) + Len (valueToFilter) — 1 queryPreText = Left (queryPreText, paramPosition) queryPostText = .CommandText queryPostText = Right (queryPostText, Len (queryPostText) - paramPosition) queryPostText = Right (queryPostText, Len (queryPostText) - InStr (queryPostText, ")") + 1) .CommandText = queryPreText & "'" & Range ("Ссылка на ячейку"). Value & "'" & queryPostText Конец с ActiveWorkbook.Connections («Имя соединения»). Конец сабвуфера  

Замените "DB_TABLE_NAME.Field_Name" и "имя соединения" (в двух местах) своими значениями (должны быть включены двойные кавычки, пробелы и знаки равенства).

Замените "ссылку на ячейку" на ячейку, к которой относится ваш параметр (пустая ячейка с самого начала) - моя ячейка была второй в первой строке, поэтому поставьте "B1" (опять же, необходимы двойные кавычки).

Сохраните и выйдите из редактора VBA.

Введите свой параметр в соответствующую ячейку.

Щелкните правой кнопкой мыши кнопку, чтобы назначить подпрограмму «Обновить» в качестве макроса, а затем нажмите кнопку. Запрос должен обновляться и отображать соответствующие данные!

Сноски: Использование полного имени параметра фильтра ("DB_TABLE_NAME.Field_Name=") необходимо только в том случае, если в вашем запросе есть объединения или другие вхождения знаков равенства, в противном случае знака равенства будет достаточно, и вычисление len() будет избыточным.Если ваш параметр содержится в поле, которое также используется для соединения таблиц, вам нужно будет изменить строку "paramPosition = InStr (queryPreText, valueToFilter) + len (valueToFilter) - 1" в коде на "paramPosition = InStr (Right (.CommandText, Len (.CommandText) - Instrev (.CommandText, "WHERE")), valueToFilter) + Len (valueToFilter) - 1 + InStr (.CommandText, "where") "поэтому он ищет только valueToFilter After "где".

Этот ответ был сделан с помощью datapig "BaconBits", где я нашел базовый код для обновления запроса.

запросов SQL и базу данных Excel? - макросы Excel для обучения VBA | Курс SQL и Oracle

Сегодня я начну свой пост необычно не с VBA или Excel, а с языка SQL. Конечно, те, кто меня знает, знают, что мы тоже будем ловить 100% Excel ;) Но идея в том, что пока я сосредоточен на том, как писать SQL-запросы. Прочитав эту статью, каждый сможет с помощью команд SQL получить то, что делает сводная таблица в Excel (или из английской сводной таблицы ;))

О чем вы узнаете из следующей статьи:

1. Что такое SQL и запросы SQL?
2. SQL-запросов в Excel
3. ВЫБЕРИТЕ запрос
4. ОТЛИЧНЫЙ
5. ГДЕ - Результат запроса фильтра
6. Операторы И и ИЛИ - уточнение результатов запроса
7. Базовые функции SQL
8. ORDER BY — сортировка результатов запроса
9. ГРУППА ПО

1.Что такое SQL и SQL-запросы?

Максимально просто: SQL — это язык , который используется для «общения» с базой данных :)
SQL-запросы — это предложения/команды, которые «говорят» базе данных, чтобы она что-то делала для нас.

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

Овладение SQL на базовом уровне действительно легко , а польза от этих знаний огромна. Если добавить щепотку VBA или даже самого Excel, спектр возможностей будет еще шире. Самое смешное, что если вы немного поработаете в Excel, некоторые вещи, о которых я пишу, покажутся вам знакомыми. Некоторые команды имеют одинаковые названия :)

Еще до того, как вы начнете учиться, я хотел вам объяснить, что единого языка SQL не существует.Так же как нет единого английского языка :) Немного другой английский например в Австралии и другой в Шотландии, вроде одинаковый и все же есть "незначительные" отличия ;). Это похоже на файлы SQL. Существует столько же разных версий SQL, сколько и разных баз данных, с которыми вы хотите общаться. Наиболее популярными базами данных являются ORACLE, SQL Server, MySQL и PostgreSQL.

Конечно, самые простые запросы будут выглядеть практически одинаково вне зависимости от базы, но... как говорится, дьявол кроется в деталях и чем дальше в лес, тем больше деревьев и т.д.:)

2. SQL-запросы в Excel

Первая проблема, с которой сталкивается большинство людей при изучении SQL-запросов, — это база данных. Приходится что-то устанавливать, настраивать, создавать таблицы, пользователей и т.д. Это обескураживает. Поэтому я и подумал, что покажу вам, как писать SQL-запросы в Excel :)

Да, старый добрый Excel может быть базой данных :) Благодаря Excel ничего не надо будет устанавливать, настраивать, добавлять пользователей, таблицы и т.д.осталось только скачать файл "baza_danych.xlsm", который я прикрепляю по ссылке ниже и... готово :)

ссылка на файл базы данных excel на гугл диске

Итак качаем файл "baza_danych.xlsm" по ссылке выше. Затем открываем его и включаем макросы. Потому что мне пришлось добавить несколько строк кода в этот файл, чтобы заставить SQL работать на нас :)

Перечень таблиц, листов следующий.
Лист результатов - включает:

• серое поле для ввода команд SQL (столбцы с J по P)
• кнопка выполнения запроса
• раздел для возврата результатов (столбцы от A до H)

Табель сотрудников - таблица с данными о сотрудниках

Ветки рабочего листа - таблица с данными о ветках
(обратите внимание, что в названии листа я не использую польские символы, это не обязательно, но... лучше перестраховаться ;)
Сервис:
Войти SQL-запрос в сером поле, нажмите кнопку "сделать запрос" и наблюдайте за результатом в листе :)
Поехали!

3.ВЫБЕРИТЕ

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

ВЫБЕРИТЕ ColumnName1, ColumnName2, ColumnName3 FROM TableName

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

ВЫБЕРИТЕ ФИО, должность, зарплату ОТ сотрудников

результат:


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

Результат:

Самое интересное, что порядок столбцов, в которых данные отображаются в таблице, не имеет значения. Именно содержимое нашего запроса «сообщает», как база данных Excel должна установить порядок представленных результатов.
Проверьте, какой результат вы получите, указав другой порядок столбцов, т.е. так:

ВЫБЕРИТЕ должность, ФИО, зарплату ОТ сотрудников

Результат:

4.ОТЛИЧНЫЙ

Вторая полезная команда SQL — DISTINCT. Это немного похоже на инструмент «Удалить дубликаты» в Excel.
После добавления в запрос предложения DISTINCT мы в результате будем видеть только уникальные строки/записи.
Посмотрите, что даст такой запрос:

ВЫБЕРИТЕ должность ИЗ сотрудников

результат:


А что вы получите, когда напишете запрос да?

ВЫБЕРИТЕ ОТЛИЧНУЮ должность ОТ сотрудников

результат:


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

5. ГДЕ - фильтрация результатов запроса

Третья команда, которая часто добавляется в SQL-запросы, — WHERE

Если вы используете Excel, вы, вероятно, уже знаете эту команду, но под другим именем = ЕСЛИ / = ЕСЛИ.Да-да, ГДЕ именно такой Excel=ЕСЛИ. С его помощью мы можем отфильтровать результат SQL-запроса или «скрыть» то, что нам не интересно.

Давайте начнем с запроса, который из нашей базы данных Excel будет перечислять имена, зарплаты и должности всех сотрудников, зарабатывающих менее 2000 (конечно, в евро;).

ВЫБЕРИТЕ ФИО, зарплату, должность ОТ сотрудников ГДЕ зарплата <= 2000 9000 3

Результат:

Теперь это другое, мы будем выбирать имена, зарплаты и должности всех людей, работающих в качестве «продавцов».Обратите внимание на апостроф вокруг слова продавец. Благодаря этим символам слово продавец будет восприниматься как текст :)

ВЫБЕРИТЕ ФИО, зарплату, должность ОТ сотрудников ГДЕ позиция = "ПРОДАВЕЦ"

Результат:

Помните, что здесь мы по-прежнему имеем дело с SQL-запросами базового уровня, но если тема вас больше интересует, проверьте, как работает оператор LIKE, используемый в WHERE, вместо знака =.

Если вы хотите отфильтровать результат запроса на основе текста, с LIKE вы можете использовать специальные символы (так называемые «шаблонные знаки»). И вот мы подошли к различиям в типах SQL, о которых я упоминал в начале. Если вы используете мой файл, то есть базу данных Excel, специальные символы будут такими же, как в базе данных Microsoft (SQL Server), то есть
%, что означает ноль или более любых символов
_ означает любой один символ и т. д.
Посмотрите, как это работает например, выполнив запрос ниже:

ВЫБЕРИТЕ ФИО, зарплату, должность ОТ сотрудников где фамилия типа 'B%'

Результат:

То есть все сотрудники, фамилия которых начинается на букву «Б».
Теперь посмотрим, как работает это заклинание:

ВЫБЕРИТЕ ФИО, зарплату, должность ОТ сотрудников ГДЕ позиция типа '%e%'

результат:


Итак, у нас есть все позиции, в которых есть буква "е" (где угодно).Я рекомендую вам поиграть с оператором LIKE, потому что понимание того, как он работает, пригодится позже в других языках программирования (например, в VBA или Python).

6. Операторы И и ИЛИ - уточнение результатов запроса

Еще одним важным элементом SQL-запросов являются операторы AND и OR. Если вы используете Excel на английском языке, то… эти слова наверняка звучат для вас знакомо. Вероятно, в ваших формулах не раз появлялись функции =И() или =ИЛИ() :) В случае людей, использующих польскую языковую версию, имеем формулы =И() и =ИЛИ() соответственно.Почему я упоминаю об этом? Потому что принцип работы этих операторов одинаков практически во всех языках программирования. Вы знаете их из Excel, вы используете их везде :)

Но для тех, кто не использовал эти формулы в Excel, напомню, что:
ИЛИ - означает, что достаточно, чтобы был истинен один элемент выражения и тогда истинно все выражение
например число = 2 число ИЛИ = 3 ИЛИ число = 4 ИЛИ число = 5 будет верно для: чисел 2,3,4,5

И - означает, что ВСЕ элементы должны быть истинными, чтобы все выражение было истинным
например.: Имя = 'ALA' И фамилия = 'Kowalska' И город = 'Krakow' будут реальными только для сотрудника: ALA Kowalska из города Кракова.

Попробуем попрактиковаться в работе этих операторов, запросив в базе данных Excel имена, зарплаты и должности всех сотрудников, работающих на должности «бухгалтер» или «аналитик»:

ВЫБЕРИТЕ ФИО, зарплату, должность ОТ сотрудников ГДЕ должность = «бухгалтер» ИЛИ должность = «аналитик»

Результат:

Такое повторение названия столбца, для которого должно быть применено условие, то есть: ГДЕ должность = «бухгалтер» или должность = «аналитик» также встречается, например, вв VBA (макросы удалось зацепить ;)
Что касается И, то мы можем проверить, как оно работает, запросив в базе данных имена, зарплаты и должности сотрудников в должности «продавец», которые зарабатывают более 1400 :

ВЫБЕРИТЕ ФИО, зарплату, должность ОТ сотрудников ГДЕ должность = 'продавец' И зарплата > 1400 9000 3

Результат:

7.ORDER BY - результат запроса сортировки

В предыдущих двух разделах (WHERE и об операторах) я показал, как использовать фильтрацию в SQL. На этом этапе вы увидите, как сортировать данные с помощью SQL.
Как вы, наверное, догадались, для этого используется команда ORDER BY :) Она работает с двумя дополнительными предложениями. Их название зависит от базы данных в которой мы работаем, в одних это будет слово DESC а в других DESCENDING но принцип работы тот же, то есть:

• ASC — сортировка по возрастанию (в Excel ей соответствует кнопка A -> Z)
• DESC — сортировка по убыванию (в Excel соответствует кнопке Z -> A)

Итак, мы хотим отобразить имена сотрудников и их зарплаты, отсортированные по человеку с самым высоким доходом.Вы уже знаете, что нам нужно запросить у базы столбцы имени и зарплаты, но дополнительно в конце запроса мы добавим наш ORDER BY

ВЫБЕРИТЕ ФИО, зарплату ОТ сотрудников СОРТИРОВАТЬ ПО зарплате DESC

Результат:

8. Основные функции языка SQL.

Что такое функция SQL? Проще говоря, это то же самое, что и функция в Excel :) Это волшебное слово/заклинание, которое возвращает конкретный результат на основе предоставленного значения/аргумента.
Чтобы было смешнее, имена функций повторяются между языками программирования или программами. Почему? Потому что если что-то работает, то… зачем это менять ;)

Например, в Excel (на английском языке) у вас есть функция =МЕСЯЦ(), результатом которой является номер месяца от заданной даты. В SQL также есть функция, которая возвращает номер месяца на основе даты.Как вы думаете, как это называется? :) ТОЧНО !!! месяц ;)
Итак, если бы я хотел посмотреть, в каком месяце он был принят на работу, какого сотрудника я могу написать такой запрос:

ВЫБЕРИТЕ фамилию, месяц (дату приема на работу) ИЗ сотрудников

результат:


Видите, как это просто? :) И таких совпадений предостаточно.
Другой пример = SUM(), который в Excel возвращает сумму из заданного диапазона. Итак, как может выглядеть SQL-запрос, который даст нам сумму зарплат всех сотрудников компании:

ВЫБЕРИТЕ сумму (зарплату) ОТ сотрудников 9000 3

ВЫБЕРИТЕ сумму (зарплату) ОТ сотрудников

Результат:


Это то же самое, что и Среднее и т.д.просто помните разницу, о которой я упоминал в языках SQL. В нашем случае надо писать AVG, а не Average, но надеюсь вы понимаете, что к чему :)

ВЫБЕРИТЕ ср (зарплата) ОТ сотрудников 9000 3

результат:


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

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

Что мы делаем в Excel, если хотим увидеть сумму заработков всех сотрудников, но с разбивкой по отдельным должностям?

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


Слово ГРУППЫ здесь ключевое. Потому что это наш следующий, очень полезный оператор, используемый в SQL-запросах :)

9. ГРУППИРОВАТЬ ПО

Если вы хотите использовать SQL-запрос для получения эффекта сводной таблицы, используйте оператор группировки и укажите SQL столбец, в котором следует группировать повторяющиеся строки. В нашей базе данных Excel есть столбец JOB в таблице «Сотрудники». Строки в этом столбце повторяются.

Если я хочу узнать, какова средняя зарплата для данной должности, я напишу такой запрос:

ВЫБЕРИТЕ должность, СРЕДНЯЯ (зарплата) ИЗ сотрудников СГРУППИРОВАТЬ ПО должности 9000 3

Результат:

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

Прежде чем вы начнете изучать, какая функция SQL может округлить результат, подумайте, как это будет выглядеть в Excel. Точно! = ОКРУГЛ (число, сколько_мест_когда)

Так что в SQL это будет очень похоже :)

ВЫБЕРИТЕ должность, ОКРУГЛ (СРЕДНЯЯ (зарплата), 2) ИЗ СОТРУДНИКОВ СГРУППИРОВАТЬ ПО должностям 9000 3

Результат:

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

Проверьте этот запрос:

ВЫБЕРИТЕ должность, ОКРУГЛ (СРЕДНЯЯ (зарплата), 2) и «PLN» ОТ сотрудников СГРУППИРОВАТЬ ПО должности

результат:


И так далее и тому подобное :)

Как я уже упоминал в начале, то, что мы здесь практиковали, является лишь введением в тему SQL.Если вам интересно, почему в заголовке второго столбца нашего результата (Expr1001) какой-то странный текст, как я могу изменить его на что-то более человечное? Или вы хотите спросить базу в каком городе работает какой сотрудник, или в каком городе вы лучше всего зарабатываете (т.е. объединить данные из нескольких таблиц в результате запроса), рекомендую воспользоваться обширными знаниями и практическим опытом моего коллеги , кто проводит обучение SQL на HintMine в различных базах :)

Например такие курсы:

А если вы хотите научиться использовать SQL в Excel, комбинировать данные из файлов Excel с запросами SQL, свяжитесь со мной по телефону:

Как присвоить результат SQL-запроса переменной Unix?

Как присвоить результат SQL-запроса переменной Unix?

В первой команде вы назначаете вывод команды даты в переменной "var"! $ () или «означает назначение вывода команды. А во второй команде вы печатаете значение переменной «var». Теперь пришло время SQL-запроса.

Как присвоить результат SQL-запроса переменной?

Синтаксис для присвоения значения переменной SQL в запросе SELECT: @имя_переменной:=значение, где имя_переменной — это имя переменной, а значение — полученное значение.Переменная может использоваться в последующих запросах везде, где разрешено выражение, например, в предложении WHERE или в операторе INSERT.

Как назначить вывод grep переменной?

Синтаксис следующий:

1. VAR = `имя-команды` VAR =" `word grep / path / to / file`" ## или ## VAR = $ (имя-команды) VAR = "$ ( слово grep /путь/к/файлу) "...
2. echo" Сегодня $(дата) "## или ## echo" Сегодня `дата`" ...
3. сегодня = $(дата) .. .
4. echo "$ сегодня"...
5. myuser="$(grep'^vivek'/etc/passwd)" echo "$myuser"

6 января.2017

Как экспортировать вывод SQL в Excel в Unix?

Вот краткий совет о том, как легко экспортировать результаты SQL в файл Excel.

1. Шаг 1: Войдите в базу данных с помощью SQL PLUS.
2. Шаг 2: Установите теги с помощью приведенной ниже команды. ВКЛЮЧИТЬ HTML-разметку.
3. Шаг 3: Кэшируйте вывод в файл. …
4. Шаг 4: Запустите запрос SQL. …
5. Шаг 5: Отключите катушку.
6. Шаг 6: Откройте выходной файл XLS, чтобы просмотреть результат.

Как запустить сценарий SQL из командной строки Unix?

Запуск сценария при запуске SQL * Plus

1. Выполните команду SQLPLUS, введя свое имя пользователя, косую черту, пробел, @ и имя файла: SQLPLUS HR @SALES. SQL * Plus запускается, запрашивает пароль и запускает сценарий.
2. Включите свое имя пользователя в первую строку файла. Следуйте за командой SQLPLUS с @ и именем файла.

Как присвоить переменную в Oracle?

Как объявить переменную и использовать ее в том же сценарии Oracle SQL?

1. Используйте раздел DECLARE и вставьте следующий оператор SELECT в BEGIN и END; . Получает доступ к переменной с & глупымваром.
2. Используйте ключевое слово DEFINE и получите доступ к переменной.
3. Использование ключевого слова VARIABLE и доступ к переменной.

25 августа. 2010

Как присвоить несколько значений переменной в SQL?

1. К вашему сведению, в SQL Server 2008 и более поздних версиях следующий синтаксис также допустим для вставки нескольких явных значений в табличную переменную: `вставить в значения @values ​​('A'), ('B' ), ('C') - Chiramisu 21 июня 13 в 23:36.
2. Осторожно, анекдот: недавно я сделал что-то подобное, чтобы упростить обслуживание запроса…

Можно ли устанавливать переменные в SQL?

Переменные в процедурах SQL определяются оператором DECLARE. Значения могут быть присвоены переменным с помощью оператора SET или оператора SELECT INTO, либо в качестве значения по умолчанию при объявлении переменной. ... Вы также можете присвоить результат оператора GET DIAGNOSTICS переменной.

Как установить переменную в операторе выбора?

Если оператор select возвращает несколько значений, переменной присваивается последнее возвращенное значение.Используйте TOP 1, если ваш запрос возвращает несколько строк. Вы можете использовать это, но помните, что ваш запрос возвращает 1 результат, несколько результатов вызовут исключение.

Каков результат grep?

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

Поддерживает ли grep регулярное выражение?

Регулярное выражение Grep

GNU grep поддерживает три синтаксиса регулярных выражений: базовый, расширенный и совместимый с Perl. В простейшей форме grep интерпретирует шаблоны поиска как базовые регулярные выражения, когда тип регулярного выражения не указан.

Как выполнить поиск?

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

Как экспортировать результаты SQL-запроса в Excel SQL Developer?

Действия по экспорту результатов запроса в Excel в Oracle SQL Developer

1. Шаг 1: Запустите запрос. Для начала вам нужно запустить запрос в SQL Developer. …
2. Шаг 2: Откройте мастер экспорта. …
3. Шаг 3: Выберите формат Excel и место, куда вы хотите экспортировать файл.…
4. Шаг 4: Экспортируйте результат запроса в Excel.

Как экспортировать данные из Unix в Excel?

Просто добавьте форматирование к файлу Unix, создайте на выходе файл с разделителями-запятыми (CSV). Теперь загрузите CSV-файл на FTP-сервер в Windows.

Как записать данные в сценарий оболочки в Excel?

Я хочу вставить данные из текстового файла в Excel с помощью сценария оболочки nawk -vr = 4 -vc = 4 -v val = $ a -F, 'BEGIN {OFS = ","}; НР!=Р; NR == r {$ c = val; Распечатайте файл.csv» Я использовал вышеизложенное, чтобы вставить значение $a в строку 4, столбец 4 в файле Excel. csv и это...

Что такое Power Query и для чего он нужен? - Курсы и обучение

Что такое Power Query?

Power Query — это технология ETL (извлечение, преобразование и загрузка), которая позволяет загружать, преобразовывать и уточнять данные для удовлетворения конкретных потребностей, связанных с широко понимаемым анализом данных. Функции Power Query доступны в Excel и Power BI Desktop. Эта технология является одной из трех, имеющихся в Excel, а также есть такие инструменты, как Power Pivot и Power Map.

Где я могу найти Power Query?

В зависимости от имеющейся у нас версии Excel Power Query может быть доступен непосредственно с определенной вкладки на ленте, или вам может потребоваться загрузить этот инструмент в виде отдельной надстройки.

Если у нас Excel 2010 или 2013, мы должны загрузить этот инструмент как дополнение к Excel — вы можете сделать это бесплатно с веб-сайта Microsoft — ссылка ЗДЕСЬ. После установки дополнения на ленте появится новая вкладка, из которой у нас будет доступ к инструменту.К сожалению, Microsoft больше не поддерживает эти версии Excel, поэтому, если мы хотим быть в курсе обновлений Power Query, мы должны переключиться как минимум на версию 2016 или более позднюю.

Начиная с версии 2016, Power Query больше не надстройка, а неотъемлемая часть Excel. Мы можем найти их на вкладке Данные , в разделе Загрузка и преобразование (для версии 2016) или Загрузка и преобразование данных (для версии 2019/365).

Однако, если у нас есть версия Excel старше 2010 года, мы не можем воспользоваться преимуществами инструмента Power Query.Единственный выход из этой ситуации — получить более новую версию Excel.

Как работает Power Query?

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

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

1. Установление соединения, то есть установление соединения с локальными данными, в облаке или другом сервисе,
2. Преобразование - изменение данных по мере необходимости, но без их повреждения в исходном источнике,
3. Объединение данных - создание данных модель из нескольких источников,
4. Совместное использование - возможность сохранить запрос, поделиться им или использовать его для создания отчета в виде, например.сводная таблица.

Создать соединение

Чтобы иметь возможность редактировать данные, нам сначала нужно импортировать их в Power Query. Мы можем подключиться к одному источнику данных, например к книге Excel, или к нескольким источникам, таким как базы данных, источники Azure и облачные службы.

Импорт выполняется нажатием кнопки Загрузка данных на вкладке Данные или прямым нажатием соответствующей кнопки в разделе Загрузка и преобразование данных .К источникам относятся данные из Интернета, файлов, баз данных, Azure, онлайн-сервисов, других источников и даже таблиц в книге Excel.

После подключения к источнику данных будет отображаться панель предварительного просмотра ( Navigator ) данных, с которыми вы имеете дело. Если мы хотим анализировать и преобразовывать данные, мы должны выбрать кнопку Transform data (в зависимости от версии Power Query кнопка может называться Edit ).

Конвертировать

Эффективный анализ данных чаще всего заключается в преобразовании наших данных, т.е.перестановка столбцов, изменение порядка строк, скрытие ненужных значений (фильтрация), изменение типов данных и т. д. Все это и многое другое возможно с Power Query Editor (также можно встретить название Query Editor ). Это специальное окно, в котором отображаются преобразования данных и каждое наше действие фиксируется в виде так называемого шаги. Благодаря им у нас есть возможность отменить, повторить данное действие, изменить порядок или модифицировать любой шаг — все для того, чтобы легко достичь намеченной цели.

Окно редактора похоже на окно Excel, оно также состоит из ленты с несколькими вкладками, и в каждой из них у нас есть разделы с различными вариантами преобразования данных. Интересно, что в Power Query мы не найдем такой кнопки, как назад — но у нас есть возможность удаления использованных шагов , что дает аналогичный эффект. Если мы хотим отредактировать шаг, у нас также есть эта опция, просто нажмите на значок шестеренки рядом с одним из шагов, и вместо удаления действия мы изменим его.

Power Query позволяет выполнять множество типов преобразований, которые можно найти на вкладках Главная , Преобразование или Добавить столбец , дополнительно у нас есть возможность создавать собственные преобразования на языке M (это язык, используемый в Power Query). Такие изменения можно сделать в Расширенном редакторе , который находится на вкладке Главная , в разделе Запрос .

Положение

Преобразования, примененные к объединенным данным, т. е. наши примененные шаги: запрос .Мы можем отобразить все запросы в данной книге Excel из панели Запросы и соединения , которая появится после нажатия одноименной кнопки на вкладке Данные , раздел Запросы и соединения .

Запрос можно вернуть на лист (т.е. загрузить ), отредактировать (тогда мы перейдем в редактор Power Query) или просто удалить - все эти варианты появляются при наведении мышки на имя нашего запрос.

Кроме того, у нас также есть возможность предоставления данных результатов. Одним из них является загрузка в , где мы можем выбрать место и способ отображения данных на листе (например, данные в виде таблицы или отчет в виде сводной таблицы).

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

Зачем использовать Power Query?

Потому что это инструмент, который значительно облегчает работу с большими наборами данных. Он работает эффективно, он может повторить все наши шаги, хранящиеся в запросе (только данные Обновить ), что позволяет нам отказаться от построения сложных и трудоемких макросов.Чтобы в полной мере ощутить всю мощь Power Query, лучше всего посмотреть их в действии, поэтому ниже вы можете просмотреть наш веб-семинар по Power Query.

Просмотреть веб-семинар Power Query в Excel

90 125

См. также

Обучение:

Как писать SQL-запросы Microsoft Access с нуля

, пожалуй, самый мощный инструмент во всем пакете Microsoft Office, однако он озадачивает (а иногда и пугает) опытных пользователей Office. Для более крутой кривой обучения, чем Word или Excel, как кто-то может обернуть голову, используя этот инструмент? На этой неделе Брюс Эппер рассмотрит некоторые вопросы, поднятые этим вопросом от одного из наших читателей.

Читатель спрашивает:

У меня проблема с написанием запроса в Microsoft Access.

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

Я хочу узнать, какие продукты в таблице A можно найти в таблице B. Я хочу добавить столбец с именем «Результаты», который содержит название продукта из таблицы A, если он существует, и название продукта из таблицы B, если он не существует в таблице A.

Есть ли у вас какие-либо советы?

Ответ Брюса:

Microsoft Access — это система управления базами данных (СУБД), разработанная для использования на компьютерах Windows и Mac.Он использует механизм базы данных Microsoft Jet для обработки и хранения данных. Он также предоставляет графический интерфейс для пользователей, который почти устраняет необходимость понимать язык структурированных запросов (SQL).

SQL — это командный язык для добавления, удаления, обновления и возврата информации, хранящейся в базе данных, а также для изменения основных компонентов базы данных, таких как добавление, удаление или изменение таблиц или индексов.

Начальная точка

Если вы новичок в Access или любой другой СУБД, я советую вам начать с этих ресурсов, прежде чем продолжить:

• Что такое база данных? Так что же такое база данных? [MakeUseOf объясняет] Так что же такое база данных? [MakeUseOf объясняет] Для программиста или энтузиаста технологии концепция базы данных может восприниматься как нечто само собой разумеющееся.Тем не менее, для многих людей сама концепция базы данных кажется немного странной… Райан Дьюб использует Excel для представления основ реляционных баз данных.
• Краткое руководство по Microsoft Access 2007 Краткое руководство по Microsoft Access 2007 Краткое руководство по Microsoft Access 2007, которое представляет собой общий обзор Access и компонентов, составляющих базу данных Access.
• Краткое руководство по таблицам Microsoft Access 2007 Краткое руководство по таблицам Microsoft Access 2007 Краткое руководство по таблицам Microsoft Access 2007 посвящено созданию вашей первой базы данных и таблиц для хранения структурированных данных.
• Краткое руководство по Microsoft Access 2007 Query Краткое руководство по Microsoft Access 2007 Query В кратком руководстве по Microsoft Access 2007 Query рассматривается, как возвращать определенные фрагменты данных, хранящиеся в таблицах базы данных.

Базовое понимание терминов в этих статьях облегчит понимание следующего.

Связь с базой данных и стандартизация

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

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

Если Джоан Смит выйдет замуж за Теда Бейнса и возьмет его фамилию, каждая строка, содержащая ее имя, теперь должна быть изменена. Проблема усугубляется, если у вас есть два разных клиента по имени «Джоан Смит». Стало намного сложнее поддерживать согласованность данных о продажах из-за довольно частых инцидентов.

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

Глядя на клиентскую часть нашего примера, мы удаляем столбцы «Имя клиента» и «Адрес клиента» и помещаем их в новую таблицу. На изображении выше я лучше разложил вещи для более подробного доступа к данным. Новая таблица также включает столбец для первичного ключа (ClientID) — число, которое будет использоваться для доступа к каждой строке в этой таблице.

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

Теперь, когда Джоан Смит меняет свое имя на Джоан Бэйнс, это изменение нужно сделать только один раз в таблице Customer. Любая другая ссылка из связанных таблиц будет извлекать соответствующее имя клиента, а отчет, в котором анализируется, что Джоан купила за последние 5 лет, будет получать все заказы как на девичью, так и на супружескую фамилию без необходимости изменения способа создания отчета.

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

Типы объединения

SQL определяет пять различных типов соединения: ВНУТРЕННЕЕ, ЛЕВОЕ ВНЕШНЕЕ, ПРАВОЕ ВНЕШНЕЕ, ПОЛНОЕ ВНЕШНЕЕ и ПЕРЕКРЕСТНОЕ. Ключевое слово OUTER является необязательным в операторе SQL.

Microsoft Access позволяет использовать ВНУТРЕННИЙ (по умолчанию), ЛЕВЫЙ ВНЕШНИЙ, ПРАВЫЙ ВНЕШНИЙ и КРЕСТ. FULL EXTERNAL как таковой не поддерживается, но с помощью LEFT OUTER, UNION ALL и RIGHT OUTER его можно подделать за счет большего количества циклов ЦП и операций ввода-вывода.

Результат перекрестного соединения содержит каждую строку левой таблицы в паре с каждой строкой правой таблицы. Единственный раз, когда я видел CROSS-соединение, это при тестировании нагрузки на серверы баз данных.

Давайте посмотрим, как работают базовые муфты, а затем изменим их в соответствии с нашими потребностями.

Давайте начнем с создания двух таблиц, ProdA и ProdB, со следующими свойствами дизайна.

AutoNumber — это автоматически увеличивающееся длинное целое число, которое присваивается записям, добавляемым в таблицу.Параметр «Текст» не был изменен, поэтому он принимает текстовую строку длиной до 255 символов.

Теперь заполните их некоторыми данными.

Чтобы показать различия в работе трех типов соединений, я удалил элементы 1, 5 и 8 из ProdA.

Затем создайте новый запрос, выбрав Create > Query Design . Выберите обе таблицы в диалоговом окне «Показать таблицу» и нажмите «Добавить », затем «Рядом с ».

Щелкните ProductID в таблице ProdA, перетащите его на ProductID в таблице ProdB и отпустите кнопку мыши, чтобы создать связь между таблицами.

Щелкните правой кнопкой мыши линию между таблицами, представляющими взаимосвязи между элементами, и выберите «Объединить свойства» .

Тип комбайна 1 (ВНУТРЕННИЙ) выбран по умолчанию. Вариант 2 — это левый внешний вызов, а вариант 3 — правый внешний вызов.

Сначала мы рассмотрим соединение INNER, поэтому нажмите OK, чтобы закрыть диалоговое окно.

В конструкторе запросов выберите поля, которые вы хотите видеть, из раскрывающихся списков.

Когда мы запустим запрос (красный восклицательный знак на ленте), он покажет поле ProductName из обеих таблиц со значением из таблицы ProdA в первом столбце и ProdB во втором.

Обратите внимание, что в результатах отображаются только значения, для которых Product ID в обеих таблицах совпадает. Хотя ProductID = 1 находится в таблице ProdB, он не отображается в результатах, поскольку ProductID = 1 не существует в таблице ProdA. То же самое верно для ProductID = 11.Существует в таблице ProdA, но не существует в таблице ProdB.

С помощью кнопки «Просмотр» на ленте и перехода к представлению SQL можно увидеть сгенерированный дизайнером SQL-запрос, используемый для получения этих результатов.

  ВЫБЕРИТЕ ProdA.ProductName, ProdB.ProductName ИЗ ВНУТРЕННЕГО СОЕДИНЕНИЯ PRODUA ProdB ON ProdA.ProductID = ProdB.ProductID;  

Вернувшись в режим конструктора, измените тип соединения на 2 (ЛЕВО ВНЕШНЕЕ). Запустите запрос, чтобы увидеть результаты.

Как видите, каждая запись в таблице ProdA представлена ​​в результатах, и только те записи в ProdB, которые имеют соответствующий ProductID в таблице ProdB, отображаются в результатах.

Пустое место в столбце ProdB.ProductName — это особое значение (NULL), так как в таблице ProdB нет соответствующего значения. Это будет важно позже.

  ВЫБЕРИТЕ ProdA.ProductName, ProdB.ProductName ИЗ PRODUA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;  

Попробуйте то же самое с третьим типом соединения (ПРАВОЕ ВНЕШНЕЕ).

Результаты показывают все в таблице ProdB, когда она показывает пустые (известные как NULL) значения, для которых таблица ProdA не имеет соответствующего значения. Пока что это приближает нас к результатам, желаемым в вопросе нашего читателя.

  ВЫБЕРИТЕ ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;  

Использование функций в запросе

Результаты функции также могут быть возвращены как часть запроса.Нам нужен новый столбец под названием «Результаты» в нашем наборе результатов. Его значением будет содержимое столбца ProductName таблицы ProdA, если ProdA равно (не NULL), в противном случае оно должно быть взято из таблицы ProdB.

Для получения этого результата можно использовать функцию Immediate IF (IIF). Функция принимает три параметра. Первое — это условие, которое должно быть оценено как True или False. Второй параметр — это значение, которое возвращается, если условие имеет значение True, а третий параметр — это значение, которое возвращается, если условие имеет значение False.

Полная конструкция функции для нашей ситуации выглядит следующим образом:

  IIF (ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName)  

Обратите внимание, что параметр условия не проверяется на равенство. Значение Null в базе данных не имеет значения, которое можно было бы сравнить с любым другим значением, включая любое другое значение Null. Другими словами, значение Null не равно Null. Всегда. Чтобы исправить это, мы проверяем значение с помощью ключевого слова «Is».

Мы могли бы также использовать «Is Not Null» и переставить параметры True и False, чтобы получить тот же результат.

Когда вы помещаете это в Query Designer, вы должны ввести всю функцию в Поле: поле. Вы должны использовать псевдоним для создания столбца «Результаты». Для этого вам нужно добавить к этой функции префикс «Результаты:», как показано на скриншоте ниже.

Эквивалентом этого кода SQL будет:

  SELECT ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) КАК РЕЗУЛЬТАТЫ СПРАВА В ПРИЛОЖЕНИИ ProdA.Pod.Идантификационный номер продукта;  

Теперь, когда мы запустим этот запрос, он выдаст следующие результаты.

Здесь мы видим для каждой записи, где таблица ProdA имеет значение, которое отражено в столбце результатов. Если в таблице ProdA нет записи, в Результаты появляется запись из ProdB, это именно то, о чем спрашивал наш читатель.

Дополнительные ресурсы для изучения Microsoft Access см. в книге Джоэла Ли Как изучить Microsoft Access: 5 бесплатных онлайн-ресурсов Как изучить Microsoft Access: 5 бесплатных онлайн-ресурсов Как изучить Microsoft Access: 5 бесплатных онлайн-ресурсов Вам нужно управлять большой объем данных? Вы должны взглянуть на Microsoft Access.Наши бесплатные учебные ресурсы помогут вам начать работу и приобрести навыки для более сложных проектов. .

Смотрите также

• 
  Что делать если не отправляются сообщения
• 
  Что такое zona
• 
  Atx блок питания включить без материнской платы
• 
  Будильник в айфоне 11
• 
  Как в ворде сделать прозрачный фон рисунка
• 
  Не открываются настройки на андроиде
• 
  Функция медиана в excel
• 
  Почему не работают динамики
• 
  Как показать клавиатуру на экране
• 
  Функция подставить в excel примеры
• 
  Компьютер видит сеть wifi но не подключается к интернету