Запрос на выборку из двух таблиц 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: запросы не проверял, возможно ошибся где нибудь.

Новые блоги и статьи Все статьи Все блоги /
сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .	Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .
Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	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 законам Кирхгофа и. . .

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

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

Решение