Запрос на выборку из двух таблиц Mysql

@EvgenNews · Регистрация: 08.03.2017

Студворк — интернет-сервис помощи студентам

Всем доброго времени. Суть вопроса такова : есть 2 таблицы - первая: Куплено вторая: Продано.
Обе таблицы строятся по принципу : одна операция = 1 строка. В строке покупок указаны :

ID операции, Тип товара, Дата, Название и Количество. (Количество Купленного)
В таблице проданного указаны Название и Количество. (Количество Проданного)

Нужно выбрать из Куплено товары с одинаковым Названием , просуммировать их количество и затем выбрать из Продано товары с теми же самыми Названиями , просуммировать их количество и вычесть первое из второго. Иначе говоря получить множество строчек с ТЕКУЩИМ количеством каждого товара по названию. Запрос выглядит :

SQL
1
2
3
4
5
6
7
8
9
10
USE DB;
SELECT `Type`, `Date`, `Title`, (SUM(col1) - SUM(col2)) 
FROM
(
SELECT  `Type`,`Date`,`Title`, `Count` AS col1 
FROM Tablebuy
UNION ALL
SELECT 0,0, `Title`,`Count` AS col2
FROM Tablesale
)x GROUP BY `Title`

Ошибка - 1054 неизвестный 'col2'. Помогите исправить.Спасибо

@Max Dark · 01.06.2017, 11:36

EvgenNews, а если так?

MySQL
1
2
3
4
select b.`type`, b.`date`, b.`title`, sum(b.`cnt`) - sum(s.`cnt`) as `diff`
    from `buys` as b
    join `sales` as s on b.`type` = s.`type`
    group by b.`type`;

@EvgenNews · 01.06.2017, 11:38 **[ТС]**

Можете пояснить запрос?

@Max Dark · 01.06.2017, 12:10

EvgenNews, так, думаю, будет правильнее

MySQL
1
2
3
4
5
6
7
8
9
10
-- выбрать покупки, объединить с продажами и вычислить остаток товаров с одинаковыми title
select b.`type`, b.`date`, b.`title`, ifnull(b.`cnt`, 0) - ifnull(s.`cnt`, 0) as `diff`
    from
        -- выбрать покупки, проссумировав количество с одинаковыми title
        (select `type`, `date`, `title`, sum(`count`) as cnt from buys group by title) as b
    left join
        -- выбрать продажи, проссумировав количество с одинаковыми title
        (select title, sum(`count`) as cnt from sales group by title) as s
        on b.`title` = s.`title`
;

@EvgenNews · 01.06.2017, 13:17 **[ТС]**

Сообщение от Max Dark

так, думаю, будет правильнее

может правильней но я мало что понял(

попробовал вот так :

SQL
1
2
3
4
5
6
7
8
9
SELECT `Type`, `Date`, `Title`, (SUM(col1) - SUM(col2)) 
FROM
(
SELECT  `Type`,`Date`,`Title`, NULL AS col1, `Count` AS col2 
FROM activesb
UNION 
SELECT 0,0, `Title`, NULL AS col2 ,`Count` AS col1
FROM activessale   
)x GROUP BY `Title`

и сразу заметил 2 проблемы - строчка (sum(col1) - sum(col2)) всегда null и почему то если у меня 3 вида купленного и не одного проданного то выводит 3 строки а если 3 вида купленного и 1 проданный то выводит 2 строки.

Добавлено через 12 минут
заменил null на 0 и столбик (sum(col1) - sum(col2)) заполнился данными. Но проблем меньше не стало - 1 база почему то возвращает не разность куплено-продано а сумму.

@Max Dark · 01.06.2017, 13:36

Сообщение от EvgenNews

но я мало что понял

что именно вам непонятно?

@EvgenNews · 01.06.2017, 16:07 **[ТС]**

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

SQL
1
2
3
4
5
6
7
8
9
SELECT `Type`, `Date`, `Title`, COALESCE(SUM(col1),0)-COALESCE(SUM(col2),0)
FROM(
SELECT  `Type`,`Date`,`Title`, `Count`  AS col1, NULL AS col2
FROM `byu` WHERE MagasinMumber= 'es3354' 
UNION ALL
SELECT 0,0,`Title`, NULL AS col1, `Count` AS col2
FROM `sale` WHERE MagasinMumber = 'es3354'
) X
GROUP BY `Title`

Я получаю несколько строк в которых указаны типы товаров, их имена и текущее количество в магазине. Все нормально. Но, положим мне нужно выбрать не все типы товаров в этом магазине а только один определенный? Казалось бы достаточно добавить еще одно условие после where вот так :

SQL
1
2
3
4
5
6
7
8
9
SELECT `Type`, `Date`, `Title`, COALESCE(SUM(col1),0)-COALESCE(SUM(col2),0)
FROM(
SELECT  `Type`,`Date`,`Title`, `Count`  AS col1, NULL AS col2
FROM `byu` WHERE MagasinMumber= 'es3354' AND TYPE = 'Milk'
UNION ALL
SELECT 0,0,`Title`, NULL AS col1, `Count` AS col2
FROM `sale` WHERE MagasinMumber = 'es3354'
) X
GROUP BY `Title`

Но тогда в запрос попадает еще одна строчка, в которой записано количество проданного : минус "-100" или "-1". Как от неё избавиться ?

@Max Dark · 01.06.2017, 23:29

Сообщение от EvgenNews

Как от неё избавиться ?

Думаю что операция union не подходит для данной задачи, так как она объединяет таблицы, добавляя строки в результат, что не позволяет группировать по нескольким столбцам из разных таблиц.
Здесь нужен join, так как он добавляет столбцы.

В вашей структуре БД таблица продаж не содержит информации о типе товара
Поле "дата" в результате содержит "мусор"

вынесем информацию о товарах в отдельную таблицу goods(id_товара, название, тип)
В таблицы покупок и продаж будем заносить только id_товара, а не название и тип

Так как нам нужны все покупки/продажи, то будем использовать соединение типа full join для таблиц продаж и покупок
Еще нам нужна группировка по паре (товар,магазин)
Для добавления информации о товаре будем использовать inner join для таблицы товаров с соединением по id_товара.
Получается следующий запрос с объединением всех данных

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
-- goods     - товары
-- purchases - закупки
-- sales     - продажи
 
-- объединяем все данные по товар+магазин
select goods.*, purchases.*, sales.*
    from purchases
    full join sales
        on purchases.goods_id = sales.goods_id and purchases.shop_id = sales.shop_id
    inner join goods 
        on purchases.goods_id = goods.id or sales.goods_id = goods.id
;

оставим только нужные поля, отфильтруем(where) по магазин+тип_товара

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
    goods.type as type,
    goods.title as title,
    ifnull(purchases.shop_id, sales.shop_id) as shop_id
    ifnull(purchases.`count`, 0) as pcnt,
    ifnull(sales.`count`, 0) as ssum
  from purchases
    full join sales
        on purchases.goods_id = sales.goods_id and purchases.shop_id = sales.shop_id
    inner join goods 
        on purchases.goods_id = goods.id or sales.goods_id = goods.id
    where
        shop_id = 'es3354' and type = 'milk'
;

ну и наконец выведем остатки в этом магазине с группировкой по товару

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
    goods.`type` as `type`,
    goods.title as title,
    sum(ifnull(purchases.`count`, 0)) - sum(ifnull(sales.`count`, 0)) as balance
  from purchases
    full join sales
        on purchases.goods_id = sales.goods_id and purchases.shop_id = sales.shop_id
    inner join goods 
        on purchases.goods_id = goods.id or sales.goods_id = goods.id
    where
        ifnull(purchases.shop_id, sales.shop_id) = 'es3354' and `type` = 'milk'
    group by
        goods.id
;

PS: запросы не проверял, возможно ошибся где нибудь.

Новые блоги и статьи Все статьи Все блоги /
Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .	Расчёт токов в цепи постоянного тока igorrr37 05.01.2026 / * Дана цепь постоянного тока с сопротивлениями и напряжениями. Надо найти токи в ветвях. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа и решает её. Последовательность действий:. . .	Новый CodeBlocs. Версия 25.03 palva 04.01.2026 Оказывается, недавно вышла новая версия CodeBlocks за номером 25. 03. Когда-то давно я возился с только что вышедшей тогда версией 20. 03. С тех пор я давно снёс всё с компьютера и забыл. Теперь. . .
Модель микоризы: классовый агентный подход anaschu 02.01.2026 Раньше это было два гриба и бактерия. Теперь три гриба, растение. И на уровне агентов добавится между грибами или бактериями взаимодействий. До того я пробовал подход через многомерные массивы,. . .	Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .	Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США. Programma_Boinc 26.12.2025 Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .	Thinkpad X220 Tablet — это лучший бюджетный ноутбук для учёбы, точка. Programma_Boinc 23.12.2025 Рецензия / Мнение/ Перевод Нашел на реддите интересную статью под названием The Thinkpad X220 Tablet is the best budget school laptop period . Ниже её машинный перевод. Thinkpad X220 Tablet —. . .

@EvgenNews 5 / 5 / 1 Регистрация: 08.03.2017 Сообщений: 229
	01.06.2017, 11:38 [ТС]
	Можете пояснить запрос? 0

Запрос на выборку из двух таблиц Mysql

Решение