首页 > 代码库 > 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 排序经典题