【Mysql之自我练习<二>】select查询（注：私用的，为了不遗失）

表结构：
table1: id ,amount
table2 : shop_id ,order_id 
table3: user_id ,mobile
table4: shop_id ,user_id


-- 查询第20到30条记录中最大的amount，存在顺序的id
select max(a.amount) from table1 a  where a.id>20 and   a.id<30;

-- 查询第20到30条记录中最大的amount，不存在顺序的id
-- 方法1：
select max(a.amount) from (SELECT * from table1  LIMIT 20,10) a ; 
-- 方法2：
select a.amount from (select * from table1 limit 20,10) a order by a.amount desc limit 1;


-- 查询销量排名前100名手机用户
-- 方法1：
select b.mobile, a.user_id, count(a.order_id) as cnt from table2 a left join table3 b on a.user_id=b.user_id 
where b.user_id>0 group by b.user_id order by cnt desc limit 100;
-- 方法2：
SELECT b.mobile,count(a.order_id) from table2 a LEFT JOIN table3 b on a.user_id=b.user_id  where  b.mobile >0 
GROUP BY b.user_id  order by count(a.order_id) DESC  limit 100;

-- 查出销量前100的店铺 
SELECT b.shop_name,a.shop_id,b.user_id,COUNT(order_id) from table2 a LEFT JOIN table4 b on  a.shop_id=b.shop_id GROUP BY b.shop_id ORDER BY COUNT(order_id) DESC LIMIT 100;


-- 查出销量前100的店铺老板的手机号码
SELECT c.mobile from table3 c where c.user_id in(SELECT b.user_id from table2 a LEFT JOIN table4 b 
on a.shop_id=b.shop_id GROUP BY b.shop_id ORDER BY COUNT(order_id) DESC ) LIMIT 100;