MySQL的标量子查询_mysql -标量子查询-案例

标量子查询

把价格最高的商品取出来???

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;

原文：https://www.cnblogs.com/zhang19950924/p/13218264.html