MySQL. Как выводить данные с максимальным значением с уже используемой сортировкой?

Question

В общем суть в следующем: Есть warehouses, goods, brands, products, это все таблицы.

Задание: нужно вывести те продукты (product.name), остатки по которым, суммарно по всем складам превышают 100ед (qoods.quantity) с указанием склада(warehouse.name) на котором находится наибольшее кол-во единиц.

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

select prod.name as product_name, war.name as warehouse_name, sum(goods.quantity) as sum_goods
from goods
  join product as prod
  on goods.product_id = prod.id
  join warehouse as war
  on goods.warehouse_id = war.id
    join brand
    on prod.brand_id = brand.id

    group by product_name 
    having sum(goods.quantity)>100      
;

вывод вот такой:

 2p suit     Гоша             150
 Shirt       Светлана         300
 Shoes       Гриша            105

каждый товар может быть на нескольких складах, группируем вывод на товару и суммируем общее количество товаров, т.е. колонка WAREHOUSE схлопывается и выводится название одного из складов, на котором есть этот товар, а нужно вывести название складов, на котором МАКСИМАЛЬНОЕ количество этого товара.

склады в данном случае назначены не по максимальному значению, а, наверное, рандомно.

Код:

create database if not exists project1;
use project1;

create table if not exists warehouse(
    id int unsigned not null auto_increment,
    name varchar(50) not null,
    
    primary key (id)    
);

insert into warehouse(name) values
('Гоша'),
('Гриша'),
('Людмила'),
('Юрий'),
('Светлана'),
('Вячеслав');


create table if not exists brand(
    id int unsigned not null auto_increment,
    name varchar(50) not null,
    country varchar(50) not null,
    
    primary key (id)
);

insert into brand (name, country) values
('Alex', 'Rus'),('Max', 'Bel'),('Den', 'Kaz'),('Kate', 'UK'),('Jhon', 'US'), ('Smit', 'DE');



create table if not exists product(
    id int unsigned not null auto_increment,
    name varchar(50) not null,
    brand_id int unsigned not null,
    
    primary key (id),
    foreign key (brand_id) references brand(id)
);

insert into product (name, brand_id) values
('2p suit', 1),('3p suit',2),('Jacket',3),('Trousers',4),('Shirt',5),('Overcoat',6),('Shoes',6),('Jeans',6);


create table if not exists goods(
    id int unsigned not null auto_increment,
    quantity int unsigned not null,
    warehouse_id int unsigned not null,
    product_id int unsigned not null,
    
    primary key (id),
    foreign key (warehouse_id) references warehouse(id),
    foreign key (product_id) references product(id)
);


insert into goods (quantity, warehouse_id, product_id) values
(150, 1, 1),(2, 2, 2),(3, 3, 3),(0, 4, 4),(300, 5, 5),(10, 4, 6),(15, 2, 7),(5, 1, 8),(90, 4, 7);

результат должен быть:

prod_name   warehouse_name   sum_goods
 2p suit     Гоша             150
 Shirt       Светлана         300
 Shoes       ЮРИЙ             105

Answer 1

Видимо как-то так

SELECT p.name, w.name, qmax, s
FROM (
    SELECT product_id, max(quantity) AS qmax , sum(quantity) as s
    FROM goods
    GROUP BY  product_id
    HAVING sum(quantity) > 100
  ) as d
INNER JOIN product AS p on p.id = product_id  
INNER JOIN goods AS g ON g.product_id = d.product_id and qmax = g.quantity
INNER JOIN warehouse AS w ON w.id = g.warehouse_id

сначала выбираете товары с нужной суммой остатков, а потом к этому делу джойните все остальное. В данном случае, если будет два склада с максимальным числом остатков, то будут выведены оба.

фиддл https://www.db-fiddle.com/f/qyYTTUUTTCnwb63dYpPfVE/0

Answer 2

Это view

drop view if exists asa;
create view asa as  
select prod.name as product_name, product_id , war.name as warehouse_name, sum(goods.quantity) as sum_goods
from goods
    join product as prod
    on goods.product_id = prod.id 
    join warehouse as war
    on goods.warehouse_id = war.id 
        group by product_name 
        having sum(goods.quantity)>100

Это сам вывод

FROM (
    SELECT product_id, max(quantity) AS qmax 
    FROM goods
    GROUP BY  product_id
    HAVING sum(quantity) > 100
  ) as d
INNER JOIN product AS p on p.id = product_id  
INNER JOIN goods AS g ON g.product_id = d.product_id and qmax = g.quantity
INNER JOIN warehouse AS w ON w.id = g.warehouse_id
inner join asa on asa.product_id = d.product_id 
;

БЛОГ НА HUSL

MySQL. Как выводить данные с максимальным значением с уже используемой сортировкой?

Ответы (2 шт):