Сложный sql запрос – Как думать на SQL? / Habr - Блог сумасшедшего сисадмина

Содержание

SQL-запрос на выборку из нескольких таблиц

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

Когда мы с Вами делали выборку из одной таблицы, то всё было очень просто:

SELECT названия_нужных_полей FROM название_таблицы WHERE условие_выборки

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

Давайте рассмотрим такой запрос:

SELECT * FROM table_1, table_2 WHERE table_1.id > table_2.user_id

Многим, кто не занимался подобными запросами, покажется, что всё очень просто, подумав, что здесь добавились только названия таблиц перед названиями полей. Фактически, это позволяет избежать противоречий между одинаковыми именами полей. Однако, сложность не в этом, а в

алгоритме работы подобного SQL-запроса.

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

Алгоритм работы следующий: берётся первая запись из table_1. Берётся id этой записи из table_1. Дальше полностью смотрится таблица table_2. И добавляются все записи, где значение поля user_id меньше id выбранной записи в table_1. Таким образом, после первой итерации может появиться от 0 до бесконечного количества результирующих записей. На следующей итерации берётся следующая запись таблицы

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

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

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

. А теперь же разберём более приземистый SQL-запрос. Допустим, у нас есть две таблицы: с товарами (есть поле owner_id, отвечающего за id владельца товара) и с пользователями (есть поле id). Мы хотим одним 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://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: 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

Сложный sql запрос – Как думать на SQL? / Habr