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

来源：互联网发布：华师大网络教育编辑：程序博客网时间：2024/05/06 18:12

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

0 0