标量子查询
把价格最高的商品取出来???
select * from goods order by goods_price desc limit 1;
可是价格最高的可能有很多条数据呢?
所以,换一种方式
1,查出最高的价格
select max(goods_price) from goods;
2,根据最高价查询
select * from goods where goods_price = (select max(goods_price) from goods);
列子查询
1,查询一列
select brand_id from brand;
2,select * from goods where brand_id in (select brand_id from brand);
行子查询
我要查出价格最高而且brand_id最大的商品
1,得到最高价,得到最大brand_id
select max(goods_price),max(brand_id) from goods;
2,根据条件查询
select * from goods where (goods_price, brand_id)=(select max(goods_price),max(brand_id) from goods);
表子查询
查出每个品牌(brand_id)最低价的一个
1,可以使用统计函数
select goods_name,min(goods_price) from goods group by brand_id;
2,使用表子查询,,不使用任何统计函数
2.1,先查出每个品牌的最低价
select * from goods order by goods_price asc;
2.2,跟聚这个表再分组
select * from (select * from goods order by goods_price asc) as g group by brand_id;
有点问题, 先略过这里---------------------------------------------------------------------------
3,使用自然内连接
1,查出每个品牌的最低价
select brand_id,min(goods_price) as goods_price from goods group by brand_id;
select * from goods natural join (select brand_id,min(goods_price) as goods_price from goods group by brand_id) as b;