mysql取到组内的前几条数据

1、准备表

CREATE TABLE `file`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folderid` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `createrid` int(11) NULL DEFAULT NULL,
  `creattime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `folderid`(`folderid`) USING BTREE,
  INDEX `createrid`(`createrid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 182 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `folder`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `createrid` int(11) NULL DEFAULT NULL,
  `creattime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `createrid`(`createrid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2、准备数据

文件夹数据
INSERT INTO `folder` VALUES (1, '文件夹1', 1, '2019-01-23 11:05:07');
INSERT INTO `folder` VALUES (2, '文件夹2', 1, '2019-01-23 11:05:21');
INSERT INTO `folder` VALUES (3, '文件夹3', 1, '2019-01-23 11:05:30');
INSERT INTO `folder` VALUES (4, '文件夹4', 2, '2019-01-23 11:05:41');
INSERT INTO `folder` VALUES (5, '文件夹5', 2, '2019-01-23 11:05:51');
INSERT INTO `folder` VALUES (6, '文件夹6', 2, '2019-01-23 11:06:00');
INSERT INTO `folder` VALUES (7, '文件夹7', 3, '2019-01-23 11:06:10');
INSERT INTO `folder` VALUES (8, '文件夹8', 3, '2019-01-23 11:06:20');
INSERT INTO `folder` VALUES (9, '文件夹9', 3, '2019-01-23 11:06:30');
INSERT INTO `folder` VALUES (10, '文件夹10', 4, '2019-01-23 11:06:41');
INSERT INTO `folder` VALUES (11, '文件夹11', 4, '2019-01-23 11:06:51');
INSERT INTO `folder` VALUES (12, '文件夹12', 4, '2019-01-23 11:07:00');
INSERT INTO `folder` VALUES (13, '文件夹13', 5, '2019-01-23 11:07:10');
INSERT INTO `folder` VALUES (14, '文件夹14', 5, '2019-01-23 11:07:24');
INSERT INTO `folder` VALUES (15, '文件夹15', 5, '2019-01-23 11:07:33');

文件数据
INSERT INTO `file` VALUES (1, 1, '文件1', 1, '2019-01-23 11:08:03');
INSERT INTO `file` VALUES (2, 1, '文件2', 1, '2019-01-23 11:08:14');
INSERT INTO `file` VALUES (3, 1, '文件3', 1, '2019-01-23 11:08:39');
INSERT INTO `file` VALUES (4, 2, '文件4', 1, '2019-01-23 11:09:02');
INSERT INTO `file` VALUES (5, 3, '文件5', 1, '2019-01-23 11:09:13');
INSERT INTO `file` VALUES (6, 3, '文件6', 1, '2019-01-23 11:09:24');
INSERT INTO `file` VALUES (7, 3, '文件7', 1, '2019-01-23 11:09:36');
INSERT INTO `file` VALUES (8, 4, '文件8', 2, '2019-01-23 11:10:17');
INSERT INTO `file` VALUES (9, 5, '文件9', 2, '2019-01-23 11:11:13');
INSERT INTO `file` VALUES (10, 6, '文件10', 2, '2019-01-23 11:13:22');
INSERT INTO `file` VALUES (11, 4, '文件11', 2, '2019-01-23 11:13:22');
INSERT INTO `file` VALUES (12, 5, '文件12', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (13, 7, '文件13', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (14, 8, '文件14', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (15, 9, '文件15', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (16, 7, '文件16', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (17, 8, '文件17', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (18, 10, '文件18', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (19, 11, '文件19', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (20, 12, '文件20', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (21, 10, '文件21', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (22, 11, '文件22', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (23, 12, '文件23', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (24, 12, '文件24', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (25, 13, '文件25', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (26, 14, '文件26', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (27, 15, '文件27', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (28, 13, '文件28', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (29, 14, '文件29', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (30, 15, '文件30', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (31, 13, '文件31', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (32, 14, '文件32', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (33, 15, '文件33', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (34, 13, '文件34', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (35, 14, '文件35', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (36, 1, '文件36', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (37, 2, '文件37', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (38, 3, '文件38', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (39, 4, '文件39', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (40, 5, '文件40', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (41, 6, '文件41', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (42, 7, '文件42', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (43, 8, '文件43', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (44, 9, '文件44', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (45, 10, '文件45', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (46, 11, '文件46', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (47, 12, '文件47', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (48, 13, '文件48', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (49, 14, '文件49', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (50, 15, '文件50', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (51, 1, '文件51', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (52, 2, '文件52', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (53, 3, '文件53', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (54, 4, '文件54', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (55, 5, '文件55', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (56, 6, '文件56', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (57, 7, '文件57', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (58, 8, '文件58', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (59, 9, '文件59', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (60, 10, '文件60', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (61, 11, '文件61', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (62, 12, '文件62', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (63, 13, '文件63', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (64, 14, '文件64', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (65, 15, '文件65', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (66, 1, '文件66', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (67, 2, '文件67', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (68, 3, '文件68', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (69, 4, '文件69', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (70, 5, '文件70', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (71, 5, '文件71', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (72, 7, '文件72', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (73, 8, '文件73', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (74, 9, '文件74', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (75, 10, '文件75', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (76, 11, '文件76', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (77, 12, '文件77', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (78, 13, '文件78', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (79, 14, '文件79', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (80, 15, '文件80', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (81, 1, '文件81', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (82, 2, '文件82', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (83, 3, '文件83', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (84, 4, '文件84', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (85, 5, '文件85', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (86, 6, '文件86', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (87, 7, '文件87', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (88, 8, '文件88', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (89, 9, '文件89', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (90, 10, '文件90', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (91, 11, '文件91', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (92, 12, '文件92', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (93, 13, '文件93', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (94, 14, '文件94', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (95, 15, '文件95', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (96, 1, '文件96', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (97, 2, '文件97', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (98, 3, '文件98', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (99, 4, '文件99', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (100, 5, '文件100', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (101, 6, '文件101', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (102, 7, '文件102', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (103, 8, '文件103', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (104, 9, '文件104', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (105, 10, '文件105', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (106, 11, '文件106', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (107, 12, '文件107', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (108, 13, '文件108', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (109, 14, '文件109', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (110, 15, '文件110', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (111, 1, '文件111', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (112, 2, '文件112', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (113, 3, '文件113', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (114, 4, '文件114', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (115, 5, '文件115', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (116, 6, '文件116', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (117, 7, '文件117', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (118, 8, '文件118', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (119, 9, '文件119', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (120, 10, '文件120', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (121, 11, '文件121', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (122, 12, '文件122', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (123, 13, '文件123', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (124, 14, '文件124', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (125, 15, '文件125', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (126, 1, '文件126', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (127, 2, '文件127', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (128, 3, '文件128', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (129, 4, '文件129', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (130, 5, '文件130', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (131, 6, '文件131', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (132, 7, '文件132', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (133, 8, '文件133', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (134, 9, '文件134', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (135, 10, '文件135', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (136, 11, '文件136', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (137, 12, '文件137', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (138, 13, '文件138', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (139, 14, '文件139', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (140, 15, '文件140', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (141, 1, '文件141', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (142, 2, '文件142', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (143, 3, '文件143', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (144, 4, '文件144', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (145, 5, '文件145', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (146, 6, '文件146', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (147, 7, '文件147', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (148, 8, '文件148', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (149, 9, '文件149', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (150, 10, '文件150', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (151, 11, '文件151', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (152, 12, '文件152', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (153, 13, '文件153', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (154, 14, '文件154', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (155, 15, '文件155', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (156, 1, '文件156', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (157, 2, '文件157', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (158, 3, '文件158', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (159, 4, '文件159', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (160, 5, '文件160', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (161, 6, '文件161', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (162, 7, '文件162', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (163, 8, '文件163', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (164, 9, '文件164', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (165, 10, '文件165', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (166, 11, '文件166', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (167, 12, '文件167', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (168, 13, '文件168', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (169, 14, '文件169', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (170, 15, '文件170', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (171, 2, '文件171', 1, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (172, 5, '文件172', 2, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (173, 9, '文件173', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (174, 12, '文件174', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (175, 13, '文件175', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (176, 8, '文件176', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (177, 14, '文件177', 5, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (178, 11, '文件178', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (179, 10, '文件179', 4, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (180, 7, '文件180', 3, '2019-01-23 11:14:31');
INSERT INTO `file` VALUES (181, 13, '文件181', 5, '2019-01-23 11:14:31');

3、需求：

#修改文件表的名字
update file as t1 inner join
(select id,concat('文件',id) as new_name from file) as t2 on t1.id = t2.id set t1.name = t2.new_name;
#修改文件夹表的名字
update folder as t1 inner join 
(select id,concat('文件夹',id) as new_name from folder) as t2 on t1.id = t2.id set t1.name = t2.new_name;

#获取每个用户的后两个文件夹
select * from (
select createrid,id,name,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid userid from folder order by createrid,id desc)
as t1 where temp < 3;
#获取每个用户前两个文件夹
select * from (
select createrid,id,name,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid userid from folder order by createrid,id)
as t1 where temp < 3;
#获取每个文件夹下面的前两个文件
select * from (
select folderid,id,if(@a=folderid,@b:=@b+1,@b:=1) as temp,@a:=folderid as temp_folderid from file order by folderid,id)
as t1 where temp < 3;
#获取每个文件夹下面的后两个文件
select * from (
select folderid,id,if(@a=folderid,@b:=@b+1,@b:=1) as temp,@a:=folderid as temp_folderid from file order by folderid,id desc)
as t1 where temp < 3;
#获取每个用户的前两个文件夹及每个文件夹下的前两个文件
select createrid,folderid,id from (
select createrid,folderid,id,if(@a=folderid,@b:=@b+1,@b:=1) as temp,@a:=folderid as temp_folderid from file order by folderid,id)
as t1 where t1.temp < 3 and t1.folderid in (
select id from (
select id,createrid,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid as userid from folder order by createrid,id)
as t2 where t2.temp < 3 );
#在一张表中实现
select createrid,folderid,id from (
select createrid,id,folderid,if(@a=folderid,@b:=@b+1,@b:=1) as temp,@a:=folderid as temp_folderid from file order by folderid,id)
as t3 where t3.temp < 3
and folderid in (
select folderid from (
select createrid,folderid,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid as userid from 
(select distinct createrid,folderid from file) as t1 order by t1.createrid,t1.folderid ) as t2  where t2.temp < 3 );



#需求取到所有用户中每一个用户的前两个文件夹,每个文件夹中的前两个文件
#第一步,先取到所有的所有的用戶及文件夾
select createrid,folderid,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid as userid from 
(select distinct createrid,folderid from file) as t1
order by t1.createrid,t1.folderid;
#第二步取到所有符合條件的文件夾
select folderid from (
select createrid,folderid,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid as userid from 
(select distinct createrid,folderid from file) as t1
order by t1.createrid,t1.folderid) as t2 where t2.temp < 2;
#第三步,找到所有文件夾下面的文件
select folderid,id from (
select folderid,id,if(@a=folderid,@b:=@b+1,@b:=1) as temp,@a:=folderid as temp_folderid from file
order by folderid,id) as t3 where t3.temp <3;
#第四步组合
select createrid,folderid,id from (
select createrid,folderid,id,if(@a=folderid,@b:=@b+1,@b:=1) as temp,@a:=folderid as temp_folderid from file
order by folderid,id) as t3 where t3.temp <3 and t3.folderid in (
select folderid from (
select createrid,folderid,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid as userid from 
(select distinct createrid,folderid from file) as t1
order by t1.createrid,t1.folderid) as t2 where t2.temp < 3);
#通过文件夹表和文件表来获取
#首先找到所有满足条件的文件夹id,找到所有用户的前两个文件夹(每个用户的前两个文件夹)
select id from (
select createrid,id,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid as temp_createrid from folder order by createrid,id)
as t1 where t1.temp < 3;
#接着根据所有的文件夹id筛选出需要的数据
select createrid,folderid,id from (
select createrid,folderid,id,if(@a=folderid,@b:=@b+1,@b:=1) as temp,@a:=folderid as temp_folderid from file
order by folderid,id) as t3 where t3.temp <3 and t3.folderid in (
select id from (
select createrid,id,if(@a=createrid,@b:=@b+1,@b:=1) as temp,@a:=createrid as temp_createrid from folder order by createrid,id)
as t1 where t1.temp < 3);

posted on 2019-01-24 15:28 邓国神韵阅读(630) 评论(0) 编辑收藏举报

刷新页面返回顶部

邓国神韵

mysql取到组内的前几条数据

导航

公告