首页 > 代码库 > SQL 排序经典题
SQL 排序经典题
2016-11-23
表结构、及表数据如下
CREATE TABLE t(user_id VARCHAR(10) CHARSET utf8,TYPE VARCHAR(20) CHARSET utf8,score INT); INSERT INTO t SELECT ‘A‘ user_id,‘语文‘ AS TYPE,95 score UNION ALL SELECT ‘A‘ user_id,‘数学‘ AS TYPE,96 score UNION ALL SELECT ‘A‘ user_id,‘英语‘ AS TYPE,86 score UNION ALL SELECT ‘A‘ user_id,‘化学‘ AS TYPE,87 score UNION ALL SELECT ‘B‘ user_id,‘语文‘ AS TYPE,85 score UNION ALL SELECT ‘B‘ user_id,‘数学‘ AS TYPE,56 score UNION ALL SELECT ‘B‘ user_id,‘英语‘ AS TYPE,75 score UNION ALL SELECT ‘B‘ user_id,‘化学‘ AS TYPE,55 score UNION ALL SELECT ‘C‘ user_id,‘数学‘ AS TYPE,68 score UNION ALL SELECT ‘C‘ user_id,‘政治‘ AS TYPE,78 score ;
解法一:初级解法(只适用于面试题,不适用于生产环境)
SELECT * FROM (SELECT * FROM t WHERE user_id = ‘A‘ ORDER BY score DESC LIMIT 3) a UNION ALL SELECT * FROM (SELECT * FROM t WHERE user_id = ‘B‘ ORDER BY score DESC LIMIT 3) b UNION ALL SELECT * FROM (SELECT * FROM t WHERE user_id = ‘C‘ ORDER BY score DESC LIMIT 3) c;
解法二:关联子查询
SELECT user_id, TYPE, score FROM ( SELECT t.*, ( SELECT COUNT(*) FROM t tt WHERE tt.user_id = t.user_id AND tt.score >= t.score ) rn FROM t ) t WHERE rn <= 3;
解法三:自连接
SELECT t1.* FROM t t1, t t2 WHERE t1.user_id = t2.user_id AND t2.score >= t1.score GROUP BY t1.user_id, t1.TYPE HAVING COUNT(t2.score) <= 3 ORDER BY user_id, rn;
解法四:子查询
SELECT * FROM t a WHERE EXISTS ( SELECT COUNT(1) FROM t b WHERE b.user_id = a.user_id AND b.score > a.score HAVING COUNT(1) < 3 )
SQL 排序经典题
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。