图1:老师表数据:图2:学生表数据:我现在用左连接查询结果:我想要的结果:
说明:只要左连接查询结果: 每个老师对应前2名学生信息。表结构和数据:SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` int(11) NOT NULL,
`s_name` varchar(20) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小明', '1');
INSERT INTO `student` VALUES ('2', '小花', '1');
INSERT INTO `student` VALUES ('3', '小莉', '1');
INSERT INTO `student` VALUES ('4', '大明', '2');
INSERT INTO `student` VALUES ('5', '大花', '2');
INSERT INTO `student` VALUES ('6', '大莉', '2');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` int(11) NOT NULL,
`t_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '王老师');
INSERT INTO `teacher` VALUES ('2', '刘老师');
说明:只要左连接查询结果: 每个老师对应前2名学生信息。表结构和数据:SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` int(11) NOT NULL,
`s_name` varchar(20) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小明', '1');
INSERT INTO `student` VALUES ('2', '小花', '1');
INSERT INTO `student` VALUES ('3', '小莉', '1');
INSERT INTO `student` VALUES ('4', '大明', '2');
INSERT INTO `student` VALUES ('5', '大花', '2');
INSERT INTO `student` VALUES ('6', '大莉', '2');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` int(11) NOT NULL,
`t_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '王老师');
INSERT INTO `teacher` VALUES ('2', '刘老师');
[征集]分组取最大N条记录方法征集,及散分....
FROM
(
SELECT t1.*,
(SELECT COUNT(*)+1 FROM
(
select T.t_id, T.t_name, S.s_id, S.s_name, S.tid from 老师表 as T
left join 学生表 as S
on T.id = S.t_id
)
WHERE tid=t1.tid AND s_id<t1.s_id) AS g_id
FROM
(select T.t_id, T.t_name, S.s_id, S.s_name, S.tid from 老师表 as T
left join 学生表 as S
on T.id = S.t_id) as t1
) as T
WHERE T.g_id<=2