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;
