SQL-запрос на выборку из нескольких таблиц
Я уже писал о самых различных SQL-запросах, но пришло время поговорить и о более сложных вещах, например, SQL-запрос на выборку записей из нескольких таблиц.
Когда мы с Вами делали выборку из одной таблицы, то всё было очень просто:
SELECT названия_нужных_полей FROM название_таблицы WHERE условие_выборки
Всё очень просто и тривиально, но при выборке сразу из нескольких таблиц становится всё несколько сложнее. Одна из трудностей — это совпадение имён полей. Например, в каждой таблице есть поле id.
Давайте рассмотрим такой запрос:
SELECT * FROM table_1, table_2 WHERE table_1.id > table_2.user_id
Многим, кто не занимался подобными запросами, покажется, что всё очень просто, подумав, что здесь добавились только названия таблиц перед названиями полей. Фактически, это позволяет избежать противоречий между одинаковыми именами полей. Однако, сложность не в этом, а в
Перед тем как читать дальше, попробуйте самостоятельно сообразить, как будет работать подобный SQL-запрос. Что он должен вывести?
Алгоритм работы следующий: берётся первая запись из table_1. Берётся id этой записи из table_1. Дальше полностью смотрится таблица table_2. И добавляются все записи, где значение поля user_id меньше id выбранной записи в table_1. Таким образом, после первой итерации может появиться от 0 до бесконечного количества результирующих записей. На следующей итерации берётся следующая запись таблицы
Если Вы поняли, как это работает после первого раза, то очень здорово, а если нет, то читайте до тех пор, пока не вникните окончательно. Если Вы это поймёте, то дальше будет проще.
Предыдущий SQL-запрос, как таковой, редко используется. Он был просто дан для объяснения алгоритма выборки из нескольких таблиц
Подобный запрос заменит 2 SQL-запроса: на выборку отдельно из таблицы с товарами и из таблицы с пользователями. Вдобавок, такой запрос сразу поставит в соответствие пользователя и его товар.
Сам же запрос очень простой (если Вы поняли предыдущий):
SELECT * FROM users, products WHERE users.id = products.owner_id
Алгоритм здесь уже несложный: берётся первая запись из таблицы users. Далее берётся её id и анализируются все записи из таблицы products, добавляя в результат те, у которых owner_id равен id из таблицы users. Таким образом, на первой итерации собираются все товары у первого пользователя. На второй итерации собираются все товары у второго пользователя и так далее.
Как видите, SQL-запросы на выборку из нескольких таблиц не самые простые, но польза от них бывает колоссальная, поэтому знать и уметь использовать подобные запросы очень желательно.
Полный курс по PHP и MySQL: http://srs.myrusakov.ru/php
- Создано 18.07.2011 16:37:33
- Михаил Русаков
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
myrusakov.ru
Как составить сложный SQL запрос? — Хабр Q&A
Готово, запрос не очень приятный, но работает. Далее timestamp заменю на date. Запрос занял 0.0806 сек.1. Необходимо получить 7 записей (неделя) из БД, в каждой записи должна быть дата и кол-во подписчиков для всех аккаунтов.
SELECT `date`, SUM(`count`) AS `count` FROM ( SELECT * FROM ( SELECT DATE(FROM_UNIXTIME(`created_at`)) AS `date`, CONCAT(`account_id`, DATE(FROM_UNIXTIME(`created_at`))) AS `a`, `count_followers` FROM `account_analytics` WHERE `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY)) ORDER BY `id` DESC ) `t1` GROUP BY `a` ) `t2` GROUP BY `date` DESC
2. Необходимо получить 7 записей (неделя) из БД, в каждой записи должна быть дата и кол-во подписчиков для определённого аккаунта.
SELECT `date`, SUM(`count`) AS `count` FROM ( SELECT * FROM ( SELECT DATE(FROM_UNIXTIME(`created_at`)) AS `date`, CONCAT(`account_id`, DATE(FROM_UNIXTIME(`created_at`))) AS `a`, `count_followers` FROM `account_analytics` WHERE `account_id` = 3 AND `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY)) ORDER BY `id` DESC ) `t1` GROUP BY `a` ) `t2` GROUP BY `date` DESC
Алгоритм такой:
1. Собираются все записи за 7 дней, колонка с датой и id аккаунта сливаются в одну строку. Все записи сортируются по убыванию
2. Затем строки группируются по колонке в которой соеденена дата и id аккаунта.
3. Результат двух действий выше группируется по дате (по убыванию)
Теперь стоит вопрос об оптимизации. Получается, что первый запрос делает выборку на ~8к записей, затем вторая и третья выборка оставляет только 7 записей.
SELECT
DATE(FROM_UNIXTIME(`created_at`)) AS `date`,
CONCAT(`account_id`, DATE(FROM_UNIXTIME(`created_at`))) AS `a`,
`count_followers`
FROM
`account_analytics`
WHERE
`account_id` = 3 AND `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY))
ORDER BY `id` DESC
Я так предполагаю, что оптимизировать этот запрос нельзя, не учитывая назначение индексов и изменение типа поля created_at с int в datetime?
toster.ru
Наш вариант теста на знание SQL / Habr
У нас, как и во многих других организациях, проводится тестирование соискателей при поступлении их на работу. Основу тестирования составляет устное собеседование, но в некоторых случаях, даются также практические задания. Несколько дней назад, Руководство попросило меня подготовить набор задач на знание SQL.Разумеется, я постарался сделать задания не слишком сложными. Уровень соискателей различен и задачи, на мой взгляд, должны быть составлены таким образом, чтобы по результатам их решения можно было судить о том, насколько хорошо испытуемый знает предмет.
Также, не имело смысла давать задания на знание каких-либо особенностей тех или иных СУБД. Мы в работе используем Oracle, но это не должно создавать трудностей для соискателей знающих, например, только MS SQL или PostgreSQL. Таким-образом, использование платформо-зависимых решений не возбраняется, но и не является ожидаемым при решении задач.
Для проведения тестирования, в Oracle 11g была развернута схема, содержащая следующие таблицы:
Требовалось составить SQL-запросы, для решения следующих пяти заданий:
Задание 1Вывести список сотрудников, получающих заработную плату большую чем у непосредственного руководителяВариант ответаselect a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Задание 2Вывести список сотрудников, получающих максимальную заработную плату в своем отделеВариант ответа
select a.* from employee a where a.salary = ( select max(salary) from employee b where b.department_id = a.department_id )
Задание 3Вывести список ID отделов, количество сотрудников в которых не превышает 3 человекВариант ответа
select department_id
from employee
group by department_id
having count(*) <= 3
Задание 4Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделеВариант ответа
select a.*
from employee a
left join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where b.id is null
Задание 5Найти список ID отделов с максимальной суммарной зарплатой сотрудниковВариант ответа
with sum_salary as
( select department_id, sum(salary) salary
from employee
group by department_id )
select department_id
from sum_salary a
where a.salary = ( select max(salary) from sum_salary )
Не требовалось искать в каком-либо смысле оптимальное решение. Единственное требование: запрос должен возвращать правильный ответ на любых входных данных. Задания разрешалось решать в любом порядке, без ограничения времени. При правильном решении всех заданий, предлагалось следующее задание повышенной сложности:Дополнительное заданиеСоставить SQL-запрос, вычисляющий произведение вещественных значений, содержащихся в некотором столбце таблицыВариант ответа
select
exp(sum(ln(decode(sign(salary),0,1,-1,-salary,salary))))
*decode(mod(sum(decode(sign(salary),-1,1,0)),2),1,-1,1)
*sign(min(abs(salary)))
from employee
Разумеется, опубликованные здесь ответы не являются единственно верными. В случае, если запрос соискателя не содержит явных ошибок, результаты его выполнения (для различных наборов исходных данных) сравниваются с результатами выполнения соответствующего эталонного запроса.
habr.com
Многотабличные запросы SQL — CodeTown.ru
Здравствуйте! До сих пор в нашем курсе мы разбирали упражнения, которые оперировали данными только из одной таблицы. Сегодня мы это исправим и научимся делать запросы сразу к нескольким таблицам в одной базе данных.
Введение
Итак, в прошлых статьях, например по оператору SELECT в SQL, мы прописывали что то похожее:
SELECT cname FROM customers;
На самом деле изначально в языке SQL было предписано указывать поле, которое хотим выбрать в явном виде, а именно так:
SELECT customers.cname FROM customers;
Мы указываем через оператор доступа «точка» то поле, которое нужно. С течением времени, SQL стал гораздо умнее и понятнее, и на данный момент он сам понимает из какой таблице мы хотим выбрать поля, если речь идет о запросах с одной таблицей.
Если же обращаться к данным из нескольких таблиц, то хорошим тоном будет указывать явно поля, так как иногда в таблицах могут содержаться поля с одинаковыми названиями, а это потенциальная ошибка.
В SQL для многотабличных запросов продумали объединение таблиц несколькими способами, о них мы и поговорим подробнее.
Объединение таблиц в SQL
Начнем с самого простого способа, в котором будем явно указывать поля и условия, при котором их нужно выводить. Вот пример:
Вывести попарно продавцов и покупателей из одного города.
Поскольку, нужно вывести попарно то придется перебрать все комбинации — SQL сделает это:
SELECT salespeople.sname, customers.cname, customers.city FROM salespeople, customers WHERE salespeople.city = customers.city
sname | cname | city |
---|---|---|
Колованов | Деснов | Москва |
Плотников | Деснов | Москва |
Проворов | Деснов | Москва |
Колованов | Краснов | Москва |
Плотников | Краснов | Москва |
Проворов | Краснов | Москва |
Петров | Кириллов | Тверь |
Шипачев | Пушкин | Челябинск |
Мозякин | Лермонтов | Одинцово |
Колованов | Белый | Москва |
Плотников | Белый | Москва |
Проворов | Белый | Москва |
Колованов | Чудинов | Москва |
Плотников | Чудинов | Москва |
Проворов | Чудинов | Москва |
Мозякин | Лосев | Одинцово |
Это пример объединения таблиц с использованием явного определения полей. Такой запрос вполне понятен, но в будущем если будет возможность обойтись без префиксов, мы будем обходиться без них.
Объединение таблиц с помощью отношений
В SQL используются математические относительные выражения («=», «>»,»
Вывести пары продавец — покупатель, при условии, что у продавца комиссия ниже 20%, а у покупателя рейтинг ниже 90.
SELECT sname, comm, cname, rating FROM salespeople, customers WHERE rating < 90 and comm < 20
sname | comm | cname | rating |
---|---|---|---|
Колованов | 10 | Лермонтов | 85 |
Малкин | 18 | Лермонтов | 85 |
Колованов | 10 | Белый | 89 |
Малкин | 18 | Белый | 89 |
В наших таблицах существует 2 продавца с комиссией ниже 20% и 2 покупателя с рейтингом ниже 90 — SQL вывел все возможные комбинации.
Очевидно, что сейчас такой запрос кажется не очень полезным и применимым. Но на самом деле такие запросы могут быть использованы при фильтрации каких либо полей в веб приложении или на сайте в форме поиска. Главное, нужно понять как можно взаимодействовать сразу с несколькими таблицами в SQL.
Примеры на многотабличные запросы в SQL
1. Напишите запрос, который бы вывел список номеров Заказов, сопровождающихся именем заказчика, который создавал эти Заказы.
SELECT onum, cname FROM orders, customers WHERE orders.cnum = customers.cnum
2. Напишите запрос, который бы выдавал имена продавца и заказчика для каждого Заказа после номера Заказов.
SELECT onum, cname, sname FROM orders, customers, salespeople WHERE orders.cnum = customers.cnum and orders.snum = salespeople.snum
3. Напишите запрос, который бы выводил всех заказчиков, обслуживаемых продавцом с комиссионными выше 12%. Выведите имя заказчика, имя продавца и ставку комиссионных продавца.
SELECT cname, sname, comm FROM orders, customers, salespeople WHERE orders.cnum = customers.cnum and orders.snum = salespeople.snum and salespeople.comm > 12
4. Напишите запрос, который вычислил бы сумму комиссионных продавца для каждого Заказа заказчика с оценкой выше 90.
SELECT onum, comm FROM orders, customers, salespeople WHERE orders.cnum = customers.cnum and orders.snum = salespeople.snum and rating > 90 GROUP BY onum
5. Напишите запрос, который бы выдавал имена продавцов и заказчиков, проживающих в одном и том же городе.
SELECT sname, cname, salespeople.city FROM customers, salespeople, orders WHERE orders.cnum = customers.cnum and orders.snum = salespeople.snum and salespeople.city = customers.city
6. Напишите запрос, который бы выдавал имена продавцов и заказчиков, проживающих в одном и том же городе и суммы их приобретений.
SELECT sname, cname, salespeople.city, amt FROM customers, salespeople, orders WHERE orders.cnum = customers.cnum and orders.snum = salespeople.snum and salespeople.city = customers.city
Заключение
На этом будем заканчивать — сегодня мы познакомились с многотабличными запросами в SQL. Теперь нас ждут все более интересные и сложные запросы, но это уже в следующих темах. Если у вас остались вопросы, то оставляйте их в комментариях.
Поделиться ссылкой:
Похожее
codetown.ru