表结构:
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;
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;