求数据项的名次和某项在整个数据中名次

1 求数据项的名次

SET @counter=0;
SELECT @counter:=@counter+1 as rank, s.id,s.sname,e.item_1+e.item_2+e.item_3+e.item_4+e.item_5+e.item_6+e.item_7+e.item_8+e.item_9+e.item_10+e.item_11+e.item_12+e.item_13
+e.item_14+e.item_15++e.item_16+e.item_17+e.item_18+e.item_19+e.item_20 as mysum 
FROM exam e,student s 
WHERE s.exam_id=e.exam_id
ORDER BY mysum DESC
LIMIT 3;

2 求 某项数据在整个数据表中的名次

SET @counter=0;
SELECT stu.id,stu.sname as "姓名",ee.rank as "名次", ee.mysums as "总的分" from(
SELECT @counter:=@counter+1 as rank,e.exam_id,e.item_1+e.item_2+e.item_3+e.item_4+e.item_5+e.item_6+e.item_7+e.item_8+e.item_9+e.item_10+e.item_11+e.item_12+e.item_13
+e.item_14+e.item_15++e.item_16+e.item_17+e.item_18+e.item_19+e.item_20 as mysums 
FROM exam e
ORDER BY mysums DESC) ee, student stu 
where ee.exam_id=stu.exam_id AND stu.id=2;

---

SET @counter=0; 用户自定义的变量

当然也可以使用 select 来定义变量 SELECT @A :=0;

 

 

 

---

编写存储过程实现 查出前N条数据

BEGIN
    SET @counter=0;
    SET @sums=0;
    SET @paixu=0;
    set @rownum=0;
    set @coas=5;
    SELECT IF(@sums=mysum,@paixu,@paixu:=@paixu+1) AS rank, @sums:=mysum AS "总得分",A.id as "学生ID",A.sname AS "学生姓名"  FROM(
    SELECT @counter:=@counter+1 as rank, s.id,s.sname,e.item_1+e.item_2+e.item_3+e.item_4+e.item_5+e.item_6+e.item_7+e.item_8+e.item_9+e.item_10+e.item_11+e.item_12+e.item_13
    +e.item_14+e.item_15++e.item_16+e.item_17+e.item_18+e.item_19+e.item_20 as mysum 
    FROM exam e,student s 
    WHERE s.exam_id=e.exam_id
    ORDER BY mysum DESC)A
    LIMIT 0,cols;
END

编写 自定义函数 实现查找 前N名共有多少条数据

BEGIN
    #Routine body goes here...
    DECLARE colsnum INT ;
    
    SET @counter=0;
    SET @sums=0;
    SET @paixu=0;
    set @rownum=0;
    set @coas=5;
    
    SELECT COUNT(*) into colsnum FROM(
        SELECT IF(@sums=mysum,@paixu,@paixu:=@paixu+1) AS rank, @sums:=mysum AS "总得分",A.id as "学生ID",A.sname AS "学生姓名"  FROM(
            SELECT @counter:=@counter+1 as rank, s.id,s.sname,e.item_1+e.item_2+e.item_3+e.item_4+e.item_5+e.item_6+e.item_7+e.item_8+e.item_9+e.item_10+e.item_11+e.item_12+e.item_13
                +e.item_14+e.item_15++e.item_16+e.item_17+e.item_18+e.item_19+e.item_20 as mysum 
            FROM exam e,student s 
        WHERE s.exam_id=e.exam_id
        ORDER BY mysum DESC)A) aa
    WHERE aa.rank<=rankNum;

    RETURN colsnum;
END

---

sqlserver 和oracle 都有排名函数

MySQL没有 排名函数