写出发帖数最多的前几条记录

因为面试题中经常被问到找出发帖数最多的几个用户的记录 后来回家测试后记录下 

 user.sql 

/*
 Navicat Premium Data Transfer

 Source Server         : 33.10
 Source Server Type    : MySQL
 Source Server Version : 50556
 Source Host           : 192.168.33.10:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50556
 File Encoding         : 65001

 Date: 22/02/2019 16:57:01
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (130, 'bu', NULL);
INSERT INTO `user` VALUES (131, 'wang', '2018-12-06 10:06:01');
INSERT INTO `user` VALUES (132, 'yong', '2018-12-04 10:05:58');
INSERT INTO `user` VALUES (133, 'shun', NULL);
INSERT INTO `user` VALUES (134, 'tian', NULL);
INSERT INTO `user` VALUES (135, 'di', NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

 test.sql

/*
 Navicat Premium Data Transfer

 Source Server         : 33.10
 Source Server Type    : MySQL
 Source Server Version : 50556
 Source Host           : 192.168.33.10:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50556
 File Encoding         : 65001

 Date: 22/02/2019 16:58:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(15) DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of test
-- ----------------------------
BEGIN;
INSERT INTO `test` VALUES (1, '131', 10, NULL);
INSERT INTO `test` VALUES (2, '132', 100, NULL);
INSERT INTO `test` VALUES (3, '131', 10, NULL);
INSERT INTO `test` VALUES (4, '132', 10, NULL);
INSERT INTO `test` VALUES (5, '133', 10, NULL);
INSERT INTO `test` VALUES (6, '132', 10, NULL);
INSERT INTO `test` VALUES (7, '131', NULL, NULL);
INSERT INTO `test` VALUES (8, '132', NULL, NULL);
INSERT INTO `test` VALUES (9, '132', NULL, NULL);
INSERT INTO `test` VALUES (10, '134', NULL, NULL);
INSERT INTO `test` VALUES (11, '135', NULL, NULL);
INSERT INTO `test` VALUES (12, '130', NULL, NULL);
INSERT INTO `test` VALUES (13, '132', NULL, NULL);
INSERT INTO `test` VALUES (14, '134', NULL, NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

 

查询记录数大于某个数的先根据uid进行group后使用having添加条件

select uid ,count(*) as num from test group by uid having num >1 order by num desc limit 3;

select uid ,count(*) as num from test group by uid having num >0 order by num desc limit 5;

 

 如果用户表在另外一个表 需要关联查询 用户表结构如下

查询语句改为如下 

select uid ,count(*) as num,u.name from test as t join user as u on t.uid=u.id  group by uid having num >0 order by num desc limit 5;

select t.uid ,count(0) as num,u.name from test as t join user as u on t.uid=u.id  group by t.uid having num >0 order by num desc limit 5;