数据库Group和Join

创建category表：

create table category
(
  categoryId   int         not null
   primary key,
  categoryName varchar(50) null
);

原始数据：

origincat.png

创建product表：

create table product
(
  productId   int auto_increment
    primary key,
  productName varchar(50) null,
  categoryId  int         null,
  price       int         null,
  constraint product_category_categoryId_fk
  foreign key (categoryId) references category (categoryId)
);

create index product_category_categoryId_fk
  on product (categoryId);

原始数据：

originprod.png

只写join:

select * from `product` join category

output

只写join是做了笛卡尔积，4*2=8，每个product都拼上两种category
真正想要的是categoryId相等的情况

select * from `product` join category on product.categoryId = category.categoryId

output.png

注意：没有nike的记录，因为没有对应的categoryId
如果想把没有对应categoryId的产品也显示出来，用left join（左外连接）

select * from `product` left join category on product.categoryId = category.categoryId

output.png

也就是product中categoryId为null的产品会被显示

小插曲：mysql5.7.5以上版本group by查询问题解决：
group by 报错解决

select  * from product left join category c on product.categoryId = c.categoryId group by product.categoryId;

out.png

select  product.categoryId,categoryName, count(*) from product left join category c on product.categoryId = c.categoryId group by product.categoryId

out2.png

选择根据categoryName分类后的最便宜的产品：

select  product.categoryId,categoryName, MIN(price) from product left join category c on product.categoryId = c.categoryId group by product.categoryId

output.png

把nike对应的categoryId设置为1后,想要得到最便宜产品的名字：

select  product.categoryId,categoryName,productName, MIN(price) from product left join category c on product.categoryId = c.categoryId group by product.categoryId

wrong.png

正确写法是使用子查询：

select * from product join (
select  product.categoryId,categoryName, MIN(price) from product left join category c
    on product.categoryId = c.categoryId group by product.categoryId
  ) as cat_min
on product.categoryId = cat_min.categoryId

ok1.png

select * from product join (
select  product.categoryId,categoryName, MIN(price) as min_price from product left join category c
    on product.categoryId = c.categoryId group by product.categoryId,categoryName
  ) as cat_min
on product.categoryId = cat_min.categoryId
where product.price = cat_min.min_price

ok2.png

优化：

select product.productName,cat_min.categoryName,cat_min.min_price from product join (
select  product.categoryId,categoryName, MIN(price) as min_price from product left join category c
    on product.categoryId = c.categoryId group by product.categoryId,categoryName
  ) as cat_min
on product.categoryId = cat_min.categoryId
where product.price = cat_min.min_price

final.png