我一个朋友面试,遇见面试官问这个问题。
好久没考虑过了,想了一下用union做链接。
当时面试官又问如果不是按男女分组,如果按56个民族,union会不会效率太低了,然后又问有没有其他
的,回来给我说了,我想了一下,欢迎各位补充不足。
建表sql:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t
-- ----------------------------
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int(15) NOT NULL,
`gender` char(2) DEFAULT NULL,
`grade` int(4) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t
-- ----------------------------
INSERT INTO `t` VALUES ('1', '男', '21', '狗蛋');
INSERT INTO `t` VALUES ('2', '男', '50', '狗蛋1');
INSERT INTO `t` VALUES ('3', '男', '60', '狗蛋2');
INSERT INTO `t` VALUES ('4', '男', '69', '狗蛋3');
INSERT INTO `t` VALUES ('5', '男', '75', '狗蛋4');
INSERT INTO `t` VALUES ('6', '男', '90', '狗蛋5');
INSERT INTO `t` VALUES ('7', '女', '48', '丫蛋');
INSERT INTO `t` VALUES ('8', '女', '90', '丫蛋1');
INSERT INTO `t` VALUES ('9', '女', '81', '丫蛋2');
INSERT INTO `t` VALUES ('10', '女', '82', '丫蛋3');
INSERT INTO `t` VALUES ('11', '女', '91', '丫蛋4');
INSERT INTO `t` VALUES ('12', '女', '71', '丫蛋5');
------------------第一种-----------------------------
(SELECT *
FROM t
WHERE t.gender='男'
ORDER BY t.grade DESC limit 0,5)
UNION
(SELECT *
FROM t
WHERE t.gender='女'
ORDER BY t.grade DESC limit 0,5);
------------------第二种-----------------------------
SELECT *
FROM t a
WHERE 5>
(SELECT count(*)
FROM t
WHERE gender=a.gender
AND grade>a.grade)
ORDER BY a.gender,a.grade desc;
------------------第三种-----------------------------
SELECT *
FROM
(SELECT h.`id`,
h.`name`,
h.`grade`,
h.`gender`,
count(*) AS rank
FROM t AS h LEFT OUTER
JOIN t AS r
ON h.gender = r.gender
AND h.`grade` <= r.`grade`
GROUP BY h.`id`,h.`gender`,h.`grade`
ORDER BY h.gender,h.grade DESC) s
WHERE s.rank<6;