sql语句之子查询

-- 需求6：查询价格高于平均价的商品信息

select * from goods where price >(select avg(price) from goods);

-- 需求7：查询所有来自拼多多的商品信息，包含商品分类
select * from goods;
select * from goods left join category on goods.typeId=category.typeId where company in ('拼多多','拼夕夕');

-- select * from goods where company in('拼多多','拼夕夕');

select * from category 
inner join (select * from goods where company in('拼多多','拼夕夕')) a on category.typeId=a.typeId;


-- 需求8：查询价格在25-100之间的商品信息
-- in(范围)
select * from goods where price between 25 and 100;
select price from goods where price between 25 and 100;
select * from goods where price in(25,30,77,30,72,25);
select * from goods where price in (select price from goods where price between 25 and 100);

-- some /any
select * from goods where price =some(select price from goods where price between 25 and 100);
select * from goods where price =any(select price from goods where price between 25 and 100);

-- all
select * from goods where price =all (select price from goods where price between 25 and 100);
select * from goods where price !=all (select price from goods where price between 25 and 100);