創(chuàng)建category表：
create table category
(
categoryId int not null
primary key,
categoryName varchar(50) null
);
原始數(shù)據(jù)：
origincat.png
創(chuàng)建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);
原始數(shù)據(jù)：
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的記錄，因為沒有對應(yīng)的categoryId
如果想把沒有對應(yīng)categoryId的產(chǎn)品也顯示出來簇秒，用left join（左外連接）
select * from `product` left join category on product.categoryId = category.categoryId
output.png
也就是product中categoryId為null的產(chǎn)品會被顯示
小插曲：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
選擇根據(jù)categoryName分類后的最便宜的產(chǎn)品：
select product.categoryId,categoryName, MIN(price) from product left join category c on product.categoryId = c.categoryId group by product.categoryId
output.png
把nike對應(yīng)的categoryId設(shè)置為1后,想要得到最便宜產(chǎn)品的名字：
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
優(yōu)化：
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
