sql — Выборка из нескольких таблиц inner join
Имеется структура БД следующего вида:
Мне необходимо сделать выборку из таблицы Drivers(date, pack, directory) и Usable(system). При написании запроса у меня(скорее всего) где-то декартово произведение, ибо выдаётся большое количество строк — дубликатов. Использование distinct проблему решает, удаляя дубликаты, и я получаю нужный результат, однако я и сам понимаю, что это жутки костыль и так делать не нужно. Ниже приведён текст моего запроса, посмотрите, может быть я делаю очевидную ошибку:
select Drivers.pack Drivers.directory, Drivers.[date], Usable.[system] from Sections inner join Drivers on ( Drivers.id in ( select driverId from Sections where Sections.id in ( select sectionId from Usable where deviceId in ( select id from Devices where deviceId like "%VEN_14F1%" and deviceId like "%DEV_8880%" ) ) ) and Drivers.id = Sections. driverId ) inner join Usable on ( Usable.[system] = "6.1x64" and Usable.sectionId = Sections.id ) order by(Drivers.[date]) desc;
Подскажите решение. P.S.: код отформатировал, надеюсь, так будет удобнее читать.
UPD:
SELECT Drivers.date, Drivers.pack, Drivers.directory, Usable.system FROM Drivers INNER JOIN Sections ON Drivers.id = Sections.driverId INNER JOIN Usable ON Sections.id = Usable.sectionId inner join Devices on Usable.deviceId = Devices.id WHERE Devices.deviceId LIKE "%VEN_14F1%" AND Devices.deviceId LIKE "%DEV_8880%" AND Usable.system = '6.1x64' order by(Drivers.[date]) desc;
- sql
- sqlite
4
Попробуйте:
SELECT Drivers.date, Drivers.pack, Drivers.directory, Usable.system FROM Drivers INNER JOIN Sections ON Drivers.id = Sections.driverId INNER JOIN Usable ON Sections.id = Usable. sectionId WHERE Usable.deviceId LIKE "%VEN_14F1%" AND Usable.deviceId LIKE "%DEV_8880%" AND Usable.system = '6.1x64'
4
Использование группировки решает проблему:
group by (Drivers.directory)
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
Необходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
sql — Объединить 3 таблицы в сложном запросе
Вопрос задан
Изменён 8 лет 4 месяца назад
Просмотрен 12k раз
Привет всем. У меня есть три таблицы: games
, active\_user_games
, balance
.
games +------+---------+ | id | title | +------+---------+ | 1 | my-game | +------+---------+ | 2 | the are | +----------------+ active\_user_game +------+-----------+--------------+ | id | user_id | game_id | +------+-----------+--------------+ | 1 | 1 | 1 | +------+-----------+--------------+ | 2 | 2 | 1 | +------------------+--------------+ | 3 | 3 | 2 | +------+-----------+--------------+ | 4 | 4 | 2 | +------------------+--------------+ balance +------+-----------+--------------+------------+ | id | user_id | item_id | summ | +------+-----------+--------------+------------+ | 1 | 1 | 1 | 1000 | +------+-----------+--------------+------------+ | 2 | 2 | 1 | 1000 | +------------------+--------------+------------+ | 3 | 3 | 2 | 2000 | +------+-----------+--------------+------------+ | 4 | 4 | 2 | 2000 | +------------------+--------------+------------+
Мне нужно вывести все записи из таблицы games и добавить 2 столбца данных к каждой записи, которые вычисляются по данным из двух остальных таблиц. А именно мне нужно вывести количество участников из таблицы active\_user_game
к конкретной игре функцией count()
и сумму платежей этих участников из таблицы balance
функцией sum(balance.summ)
.
Я делаю такой запрос:
SELECT g.id, g.title, au.game_id, au.id, b.id, b.user_id, COUNT(au.game_id) AS users, SUM(b.summ) AS bank FROM games AS g JOIN active_user_game AS au ON au.game_id = g.id JOIN balance AS b ON b.item_id = g.id WHERE g.private = 0 AND g.closed = 0 AND b.type IN(5,6) GROUP BY g.id ORDER BY g.vip DESC, g.benefit DESC
Но так как в разных таблицах разное количество записей, он выводит users и bank с неправильными цифрами, помогите решить эту задачу. Спасибо.
Примерно так:
SELECT g.id, g.title, au.game_id, au.cnt AS users, b.user_id, b.summ AS bank FROM games AS g LEFT JOIN (select game_id, count(*) as cnt from active_user_game group by game_id) AS au ON au.game_id = g. id LEFT JOIN (select item_id, sum(summ) as summ from balance where type IN(5,6) group by item_id) AS b ON b.item_id = g.id WHERE g.private = 0 AND g.closed = 0 ORDER BY g.vip DESC, g.benefit DESC
Кроме того, au.id
и b.id
у вас выбирались бы по принципу, известному только mysql (вероятно первое попавшееся значение), поэтому смысла в них нет. А в других субд такой запрос просто не выполнился бы с ошибкой что-то типа «поле au.id, b.id не входят в группу».
1
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
ПочтаНеобходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
Соединение 3 таблиц в SQL
В SQL можно соединить три таблицы или более, добавив еще одно соединение после первого.
Вы также можете запускать вложенные соединения, указав одно соединение в качестве условия соединения для другого.
Синтаксис
Наиболее распространенный способ соединения трех таблиц выглядит примерно так:
SELECT * ИЗ Таблицы1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица 2 При условии ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица 3 При условии;
Здесь используется внутреннее соединение, но вы можете указать желаемый тип соединения, как и для любого другого соединения. При необходимости вы также можете комбинировать типы соединений (пример ниже).
Вы также можете использовать вложенные соединения, указав одно соединение в качестве условия соединения для другого соединения. Вот так:
ВЫБЕРИТЕ * ИЗ Таблицы1 ПРИСОЕДИНЯЙТЕСЬ (Таблица 2 ПРИСОЕДИНЯЙТЕСЬ к таблице 3 При условии) При условии;
Пример данных – 3 таблицы
Предположим, у нас есть следующие три таблицы.
Таблица Клиенты
:
+--------------+----------------+-------- -----------------------+--+ | Идентификатор клиента | ИмяКлиента | Почтовый идентификатор города | Номер телефона | |----------------------------+----------------+-+ ----------------| | 1 | Гомер Маккензи | 19586 | (308) 555-0100 | | 2 | Мардж Пратт | 33475 | (406) 555-0100 | | 3 | Влад Бернанке | NULL | (480) 555-0100 | | 4 | Барт Питт | 21692 | (316) 555-0100 | | 5 | Лиза МакКуин | 12748 | (212) 555-0100 | | 6 | Стив Симпсон | 17054 | (701) 555-0100 | | 7 | Винн Аллен | 12152 | (423) 555-0100 | | 8 | Виджей Смит | 3673 | (303) 555-0100 | | 9| Кейси Чин | 23805 | (201) 555-0100 | | 10 | Борат Ли | 37403 | (701) 555-0100 | +--------------+----------------+-+ --+ (затронуто 10 строк)
Таблица Cities
:
+----------+----------------+------ -------------+--------------+ | Идентификатор города | ГородНазвание | Идентификатор штатаПровинция | Население | |----------+----------------+-----+- -------------| | 3673 | Боу Мар | 6 | 866 | | 12152 | Франкьюинг | 44 | NULL | | 12748 | Гаспорт | 33 | 1248 | | 21692 | Медицинский домик | 17 | 2009 | | 26483 | Долина Пиплз | 3 | 428 | | 33475 | Сильванит | 27 | 103 | | 17054 | Джесси | 35 | 25 | | 19586 | Лиско | 28 | NULL | | 37403 | Уимблдон | 35 | 216 | +----------+----------------+-----+- -------------+ (затронуто 9 строк)
StateProvinces 9Таблица 0024:
+---------------------------------+-------+-- -------------------+-------------+---------------+ | Идентификатор штатаПровинция | Код штатаПровинция | StateProvinceName | Идентификатор страны | Население | |-------------------+---------------------+------- --------------+--------------+--------------| | 3 | AZ | Аризона | 230 | 6891688 | | 6 | CO | Колорадо | 230 | 5698265 | | 17 | КС | Канзас | 230 | 2893957 | | 28 | NE | Небраска | 230 | 1943256 | | 31 | Нью-Джерси | Нью-Джерси | 230 | 8899339 | | 33 | Нью-Йорк | Нью-Йорк | 230 | 20437172 | | 35 | НД | Северная Дакота | 230 | 723393 | | 44 | ТН | Теннесси | 230 | 6495978 | +-------------------+----------------------+------- --------------+--------------+--------------+ (затронуто 8 рядов)
Пример 1.
Внутреннее соединение 3 таблицыНаиболее популярным типом соединения является внутреннее соединение, поэтому мы начнем с него.
Вот пример соединения трех вышеприведенных таблиц с двумя внутренними соединениями.
ВЫБЕРИТЕ s.StateProvinceName, ci.CityName, cu.CustomerName ИЗ ГосударствоПровинции s ВНУТРЕННЕЕ СОЕДИНЕНИЕ Города AS ci ON ci.StateProvinceID = s.StateProvinceID ВНУТРЕННЕЕ СОЕДИНЕНИЕ Клиенты у.е. ПО cu.PostalCityId = ci.CityId;
Результат:
+---------------------+--+---- ------------+ | StateProvinceName | ГородНазвание | ИмяКлиента | |------------------------------------+--+----------- ------| | Небраска | Лиско | Гомер Маккензи | | Канзас | Медицинский домик | Барт Питт | | Нью-Йорк | Гаспорт | Лиза МакКуин | | Северная Дакота | Джесси | Стив Симпсон | | Теннесси | Франкьюинг | Винн Аллен | | Колорадо | Боу Мар | Виджей Смит | | Северная Дакота | Уимблдон | Борат Ли | +-----------------------------------+--+----------- ------+ (затронуто 7 рядов)
Пример 2.
Комбинирование типов соединенийВы можете комбинировать типы соединений при объединении трех или более таблиц.
Вот пример объединения внутреннего соединения с левым соединением.
ВЫБЕРИТЕ s.StateProvinceName, ci.CityName, cu.CustomerName ИЗ ГосударствоПровинции s ВНУТРЕННЕЕ СОЕДИНЕНИЕ Города AS ci ON ci.StateProvinceID = s.StateProvinceID LEFT JOIN Клиенты у.е. ПО cu.PostalCityId = ci.CityId;
Результат:
--------------------+----------------+----- -----------+ | StateProvinceName | ГородНазвание | ИмяКлиента | |------------------------------------+--+----------- ------| | Колорадо | Боу Мар | Виджей Смит | | Теннесси | Франкьюинг | Винн Аллен | | Нью-Йорк | Гаспорт | Лиза МакКуин | | Канзас | Медицинский домик | Барт Питт | | Аризона | Долина Пиплз | NULL | | Северная Дакота | Джесси | Стив Симпсон | | Небраска | Лиско | Гомер Маккензи | | Северная Дакота | Уимблдон | Борат Ли | +-----------------------------------+--+----------- ------+ (затронуто 8 рядов)
В этом случае у нас есть город (Долина Пиплс), в котором еще нет клиентов.
Причина, по которой мы теперь можем видеть эту информацию, заключается в том, что левое соединение возвращает строки, содержащие данные в левой таблице, даже если в левой таблице нет соответствующих строк.
Предыдущий пример, объединяющий два внутренних соединения, не возвращал эту строку, поскольку внутренние соединения отбрасывают несовпадающие строки из обеих таблиц. Он возвращает строки только в том случае, если в обеих таблицах есть хотя бы одна строка, соответствующая условию соединения.
Новые образцы данных — 3 разные таблицы
В оставшихся примерах мы будем использовать следующие таблицы.
Таблица PetTypes
:
+-------------+-----------+ | Идентификатор типа питомца | Тип питомца | |-------------+------------| | 1 | Птица | | 2 | Кот | | 3 | Собака | | 4 | Кролик | +-------------+-----------+ (4 строки затронуты)
Таблица Pets
:
+---------+-------------+---------- -+-----------+-------------+ | Идентификатор домашнего животного | Идентификатор типа питомца | Идентификатор владельца | Имя питомца | Дата рождения | |---------+--------------+-----------+------------+- -----------| | 1 | 2 | 3 | Пушистый | 20. 11.2020 | | 2 | 3 | 3 | Получить | 2019-08-16 | | 3 | 2 | 2 | Скретч | 2018-10-01 | | 4 | 3 | 3 | Виляние | 2020-03-15 | | 5 | 1 | 1 | Твитнуть | 2020-11-28 | | 6 | 3 | 4 | Пушистый | 2020-09-17 | | 7 | 3 | 2 | Кора | NULL | | 8 | 2 | 4 | Мяу | NULL | +---------+-------------+-----------+------------+- -----------+ (затронуты 8 строк)
Таблица Владельцы
:
+-----------+-------------+------------+-------- ----------------------+-----+ | Идентификатор владельца | Имя | Фамилия | Телефон | Электронная почта | |-----------+-------------+-------------+---------- ------+-------------------| | 1 | Гомер | Коннери | (308) 555-0100 | [электронная почта защищена] | | 2 | Барт | Питт | (231) 465-3497 | [электронная почта защищена] | | 3 | Нэнси | Симпсон | (489) 591-0408 | NULL | | 4 | Борис | Трамп | (349) 611-8908 | NULL | | 5 | Вуди | Иствуд | (308) 555-0112 | [электронная почта защищена] | +-----------+-------------+------------+---------- ------+-------------------+
Обратите внимание, что:
- Столбец
PetTypeId
таблицыPets
является внешним ключомPetTypeId
таблицыPetTypes
(которая является первичным ключом этой таблицы). - Столбец
OwnerId
таблицыPets
является внешним ключомСтолбец OwnerId
таблицыOwners
.
Пример 3. Левое соединение 3 таблиц
Давайте выполним соединение трех таблиц, используя два левых соединения.
Вот пример выполнения двух левых объединений для этих таблиц.
ВЫБЕРИТЕ п.имя питомца, pt.PetType, CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner ОТ владельцев o ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к домашним животным p ON p.OwnerId = o.OwnerId LEFT JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId;
Результат:
+-----------+------------+----------------+ | Имя питомца | Тип питомца | PetOwner | |-----------+------------+----------------| | Твитнуть | Птица | Гомер Коннери | | Скретч | Кот | Барт Питт | | Кора | Собака | Барт Питт | | Пушистый | Кот | Нэнси Симпсон | | Получить | Собака | Нэнси Симпсон | | Виляние | Собака | Нэнси Симпсон | | Пушистый | Собака | Борис Трамп | | Мяу | Кот | Борис Трамп | | NULL | NULL | Вуди Иствуд | +-----------+------------+----------------+ (9строки затронуты)
Здесь у нас есть владелец домашнего животного, у которого нет домашнего животного. Мы можем убедиться в этом, взглянув на столбец Pets.OwnerId
и увидев, что в таблице Owners
нет значения, соответствующего OwnerId
Вуди Иствуда.
Пример 4. Правое объединение 3 таблиц
Правое объединение противоположно левому. Вот пример использования тех же трех таблиц.
ВЫБЕРИТЕ п.имя питомца, pt.PetType, CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner ОТ домашних животных p ПРАВО ПРИСОЕДИНЯЙТЕСЬ к владельцам o ON p.OwnerId = o.OwnerId ПРАВОЕ ПРИСОЕДИНЕНИЕ PetTypes pt ON p.PetTypeId = pt.PetTypeId;
Результат:
+-----------+------------+---------------+ | Имя питомца | Тип питомца | Владелец домашнего животного | |-----------+------------+----------------| | Твитнуть | Птица | Гомер Коннери | | Пушистый | Кот | Нэнси Симпсон | | Скретч | Кот | Барт Питт | | Мяу | Кот | Борис Трамп | | Получить | Собака | Нэнси Симпсон | | Виляние | Собака | Нэнси Симпсон | | Пушистый | Собака | Борис Трамп | | Кора | Собака | Барт Питт | | NULL | Кролик | | +-----------+------------+---------------+ (9строки затронуты)
На этот раз мы получили дополнительный тип питомца ( Кролик
), но не дополнительного владельца. Это связано с тем, что правые соединения возвращают строки, содержащие данные в правой таблице, даже если в левой таблице нет соответствующих строк.
Кстати, причина, по которой последний PetOwner
не равен NULL
(как и последний PetName
), заключается в том, что это результат конкатенации строк. Я использовал функцию T-SQL CONCAT()
для объединения имени и фамилии владельца.
Пример 5. Полное объединение 3 таблиц
Полное объединение похоже на левое и правое объединение в одной. Он возвращает все строки, если в одной из таблиц есть совпадающие данные.
ВЫБЕРИТЕ п.имя питомца, pt.PetType, CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner ОТ владельцев o ПОЛНОЕ СОЕДИНЕНИЕ Домашние животные p ON p.OwnerId = o.OwnerId ПОЛНОЕ СОЕДИНЕНИЕ PetTypes pt ON p.PetTypeId = pt.PetTypeId;
Результат:
+-----------+-----------+----------------+ | Имя питомца | Тип питомца | PetOwner | |-----------+------------+----------------| | Твитнуть | Птица | Гомер Коннери | | Скретч | Кот | Барт Питт | | Кора | Собака | Барт Питт | | Пушистый | Кот | Нэнси Симпсон | | Получить | Собака | Нэнси Симпсон | | Виляние | Собака | Нэнси Симпсон | | Пушистый | Собака | Борис Трамп | | Мяу | Кот | Борис Трамп | | NULL | NULL | Вуди Иствуд | | NULL | Кролик | | +-----------+------------+----------------+ (затронуто 10 рядов)
На этот раз мы получаем комбинацию результатов, полученных в двух предыдущих примерах.
Пример 6. Вложенные соединения
Как уже упоминалось, вы также можете выполнять вложенные соединения.
Вот пример вложенного соединения.
ВЫБЕРИТЕ п.имя питомца, pt.PetType, CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner ОТ владельцев o ЛЕВОЕ СОЕДИНЕНИЕ (Pets p LEFT JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId) ON p.OwnerId = o.OwnerId;
Результат:
+------------+------------+----------------+ | Имя питомца | Тип питомца | PetOwner | |-----------+------------+----------------| | Твитнуть | Птица | Гомер Коннери | | Скретч | Кот | Барт Питт | | Кора | Собака | Барт Питт | | Пушистый | Кот | Нэнси Симпсон | | Получить | Собака | Нэнси Симпсон | | Виляние | Собака | Нэнси Симпсон | | Пушистый | Собака | Борис Трамп | | Мяу | Кот | Борис Трамп | | NULL | NULL | Вуди Иствуд | +-----------+------------+----------------+ (9строки затронуты)
mysql - внутреннее соединение для 3 таблиц с СУММОЙ двух столбцов в SQL-запросе?
спросил
Изменено 2 года, 11 месяцев назад
Просмотрено 2к раз
У меня есть следующие три таблицы:
У меня есть следующий запрос для присоединения к 3 таблицам выше
customer. customer_id, Имя Клиента, СУММ(продажи.общая), продажи.создано_в, СУММ(продажи_платежей.сумма) ОТ продажи ВНУТРЕННЕЕ СОЕДИНЕНИЕ клиента ON customer.customer_id = sales.customer_id ВНУТРЕННЕЕ СОЕДИНЕНИЕ sales_payments ON sales.customer_id = sales_payments.customer_id ГДЕ sales.created_at = '2020-04-03' ГРУППА По имени клиента
Результат для вышеуказанного запроса приведен ниже
Сумма продаж.всего
удваивает фактическую сумму столбца sales.total, который имеет 2 строки, мне нужно иметь фактическую СУММУ этого столбца, не удваивая СУММУ этих строк. Спасибо за вашу помощь заранее..
- mysql
- sql
- sql-server
- join
ПРОБЛЕМА
Проблема в том, что существуют последовательные внутренние соединения, и количество строк, получаемых во втором внутреннем соединении, не ограничено. Итак, поскольку мы не добавили условие для sales_payment_id в соединение между таблицами продаж и sales_payment, одна строка в таблице продаж (в данном случае для customer_id 2) будет сопоставлена с двумя строками в таблице платежей. Это приводит к пересмотру одних и тех же значений. Другими словами, отображение для customer_id 2 между тремя таблицами составляет 1:1:2, а не 1:1:1.
РЕШЕНИЕ
Решение 1. Как упомянул Гордон, вы можете сначала агрегировать значения сумм в таблице sales_payments, а затем агрегировать значения в таблице продаж.
Решение 2. В качестве альтернативы (ИМХО лучший подход) вы можете добавить внешний ключ между таблицами продаж и продаж_платежа. Например, столбец sales_payment_id таблицы sales_payment также может быть введен в таблицу продаж. Это упростит соединение между этими таблицами и уменьшит дополнительные накладные расходы при запросе данных.
Тогда запрос будет выглядеть так:
`SELECT c.customer_id, имя, СУММА(s.total), s.created_at, СУММ(сумма) ОТ клиента c ВНУТРЕННЕЕ СОЕДИНЕНИЕ продаж ВКЛ c.customer_id = s.customer_id ВНУТРЕННЕЕ СОЕДИНЕНИЕ sales_payments sp ВКЛ c.customer_id = sp.customer_id И s.sales_payments_id = sp.