mysql - MySql中的分组和最大数

Question

下午好!开始研究MySql，遇到一个问题:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.product.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我用这个网站来学习https://www.db-fiddle.com/f/xxV16kmnZKmPktUdMSK6xZ/0

我的任务是:查找每种产品价格最低的区域。当有多个地区价格相同时，按字母顺序选择第一个。

我的 table :

create table product(id int, name varchar(99));
create table region(id int, name varchar(99));
create table price(productId int, regionId int, price decimal(9, 2));

insert into product values(1, 'Crab');
insert into product values(2, 'Crayfish');

insert into region values(1, 'Kiev');
insert into region values(2, 'Kharkov');
insert into region values(3, 'Lvov');

insert into price values(1, 1, 100);
insert into price values(1, 2, 100);
insert into price values(1, 3, 200);
insert into price values(2, 1, 200);
insert into price values(2, 2, 100);
insert into price values(2, 3, 100); 

我的选择:

select product.name 'product', price.price, region.name 'Region name' 
from price
left join product on product.id = price.productId
left join region on region.id = price.regionId
where price.price != 0
group by region.name
having max(price.price)
order by region.name

预期结果:

product region  price
Crab    Kharkov 100
Crayfish    Kharkov 100

Answer 1

我会使用类似的东西:

select
  p2.name as product,
  (
    select min(r.name)
    from
      price as c2
      inner join region as r on c2.regionid = r.id
    where c2.productId = t.id and c2.price = t.price) as region,
  t.price
from
  product as p2
  inner join (
    select
      p.id,
      min(c.price) as price
    from
      product as p
      left outer join price as c on p.id = c.productId
    group by p.id) as t on p2.id = t.id

主要思想是为每个产品(子查询 t)获取最小价格，然后为每个产品/价格组合找到最小(即按字母顺序排列)的区域