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

Новые блоги и статьи Все статьи Все блоги /
BOINC: 22 года — и всё ещё работает Programma_Boinc 12.03.2026 BOINC: 22 года — и всё ещё работает Дэвид Андерсон написал ретроспективу. Кратко: в 2001 году он ушёл из United Devices, где был CTO, и за несколько месяцев написал ядро BOINC — клиент, сервер,. . .	SDL3 для Desktop (MinGW): Создаём пустое окно с нуля для 2D-графики на SDL3, Си и C++ 8Observer8 10.03.2026 Содержание блога Финальные проекты на Си и на C++: hello-sdl3-c. zip hello-sdl3-cpp. zip Результат:	Установка CMake и MinGW 13.1 для сборки С и C++ приложений из консоли и из Qt Creator в EXE 8Observer8 10.03.2026 Содержание блога MinGW - это коллекция инструментов для сборки приложений в EXE. CMake - это система сборки приложений. Здесь описаны базовые шаги для старта программирования с помощью CMake и. . .	Как дизайн сайта влияет на конверсию: 7 решений, которые реально повышают заявки Neotwalker 08.03.2026 Многие до сих пор воспринимают дизайн сайта как “красивую оболочку”. На практике всё иначе: дизайн напрямую влияет на то, оставит человек заявку или уйдёт через несколько секунд. Даже если у вас. . .
Модульная разработка через nuget packages DevAlt 07.03.2026 Сложившийся в . Net-среде способ разработки чаще всего предполагает монорепозиторий в котором находятся все исходники. При создании нового решения, мы просто добавляем нужные проекты и имеем. . .	Модульный подход на примере F# DevAlt 06.03.2026 В блоге дяди Боба наткнулся на такое определение: В этой книге («Подход, основанный на вариантах использования») Ивар утверждает, что архитектура программного обеспечения — это структуры,. . .	Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. Сканируйте QR-код на мобильном. Вращайте камеру одним пальцем,. . .	SDL3 для Web (WebAssembly): Синхронизация спрайтов SDL3 и тел Box2D 8Observer8 04.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-sync-physics-sprites-sdl3-c. zip На первой гифке отладочные линии отключены, а на второй включены:. . .

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

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

Решение