举例:有10个用户,输出在订单表中下单数最多的5个人的名字。
my_order,uid对应my_user表的id:
写一个存储过程,随机插入10000条数据:
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_loop`( ) BEGIN DECLARE i INT DEFAULT 1; WHILE i < 10000 DO INSERT INTO my_order ( oid, uid ) VALUES ( CONCAT( 'o_', DATE_FORMAT( now( ), '%Y%m%d%h%i%s' ), FLOOR(1000 + RAND( )*(9999-1000) )), FLOOR( 1 + RAND( ) * 10 ) ); SET i = i + 1; END WHILE; END
查询语句:
SELECT u.id, u.`name`, count( o.uid ) count_num FROM my_user u JOIN my_order o ON u.id = o.uid GROUP BY o.uid HAVING COUNT( o.uid ) ORDER BY COUNT( o.uid ) DESC LIMIT 5;
查询结果(以自己数据为准):
统计所有的用户订单数:
SELECT u.id, u.`name`, count( o.uid ) count_num FROM my_user u JOIN my_order o ON u.id = o.uid GROUP BY o.uid HAVING COUNT( o.uid ) ORDER BY COUNT( o.uid ) DESC
结果如下:
该sql 主要考察 group by 和 having 的使用,然后 order by 和 desc 排序。