mysql复杂的存储过程

原创已于 2024-02-01 18:50:55 修改 · 285 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2024-02-01 17:26:06 首次发布

案例存储过程用来做活动的统计数据

CREATE  PROCEDURE `queryActivityExamScoreGroupCount`(in activityCode VARCHAR(50),in groupScore int)
BEGIN
  #Routine body goes here...
    DECLARE totalExamScore DOUBLE;#试卷总分
    DECLARE examinationPaperId int;#试卷id
    DECLARE numGroup int;#分几组分数段
    DECLARE i int DEFAULT(0);#循环值
    DECLARE scoreCount VARCHAR(100);#每组里数量：临时变量
    DECLARE yushu int;
    #先根据活动code查询试卷id
    select examination_paper_id into examinationPaperId from answer_activity where activity_code=activityCode;
    #再根据试卷id查询试卷总分
    select question_total_score into totalExamScore from examination_paper where id=examinationPaperId;
    set yushu=totalExamScore%groupScore;
    #根据试卷总分计算试卷分几段
    set numGroup=(totalExamScore-yushu)/groupScore;
    #定义要返回的数据
    SET @scoreGroupCountStr = '';
    IF (yushu>0 and totalExamScore>groupScore and groupScore>0) or (numGroup=0 and groupScore>0) THEN
     set numGroup=numGroup+1;
  END IF;
    #循环处理每一个分数段人数，并组装返回
    WHILE i<numGroup 
    DO
      IF i=0 THEN
         IF numGroup=1 THEN
         select count(1) into scoreCount from activity_participate where user_score>=i*groupScore and user_score<=(i+1)*groupScore and activity_code=activityCode;
           set @scoreGroupCountStr=CONCAT('[{',@scoreGroupCountStr,'"score":',(i+1)*groupScore,',','"groupRange":"≥',i*groupScore,'≤',totalExamScore,'","scoreCount":',scoreCount, '}]');
         ELSE
       select count(1) into scoreCount from activity_participate where user_score>=i*groupScore and user_score<(i+1)*groupScore and activity_code=activityCode;
           set @scoreGroupCountStr=CONCAT('[{',@scoreGroupCountStr,'"score":',(i+1)*groupScore,',','"groupRange":"≥',i*groupScore,'<',(i+1)*groupScore,'","scoreCount":',scoreCount, '},');
         END IF;
      ELSEIF i<(numGroup-1) THEN
       select count(1) into scoreCount from activity_participate where user_score>=i*groupScore and user_score<(i+1)*groupScore and activity_code=activityCode;
       set @scoreGroupCountStr=CONCAT(@scoreGroupCountStr,'{"score":',(i+1)*groupScore,',','"groupRange":"≥',i*groupScore,'<',(i+1)*groupScore,'","scoreCount":',scoreCount, '},');
      ELSE
       select count(1) into scoreCount from activity_participate where user_score>=i*groupScore and user_score<=(i+1)*groupScore and activity_code=activityCode;
     set @scoreGroupCountStr=CONCAT(@scoreGroupCountStr,'{"score":',(i+1)*groupScore,',','"groupRange":"≥',i*groupScore,'≤',totalExamScore,'","scoreCount":',scoreCount, '}]');
    END IF;
      set i=i+1;
    END WHILE;
    
    select @scoreGroupCountStr;
END

CREATE  PROCEDURE `queryActivityStatisticData`(
IN activityCode VARCHAR(50))
BEGIN
  /**满分人数*/ 
  DECLARE fullMarks int(11);
    /**参与总次数*/ 
    DECLARE sumNumberPartake int(11);
    /**参与总人数*/ 
    DECLARE sumUserPartake int(11);
    /**正确率*/ 
    DECLARE accuracy double(5,2);
    /**最高分*/ 
    DECLARE maxTotalScore double(5,2);
    /**最低分*/ 
    DECLARE minTotalScore double(5,2);
    /**平均分*/ 
    DECLARE avgTotalScore double(5,2);
    /**总分*/ 
    DECLARE totalScore double(5,2);
    
    /**查询满分人数*/ 
    select count(uid)  into fullMarks from activity_participate where user_score=examination_paper_question_total_score and activity_code=activityCode;
    /**查询参与总次数，参与总人数,最高分，最低分，平均分,总分*/ 
    select count(uid) ,count(DISTINCT uid),max(user_score),min(user_score),AVG(user_score),max(examination_paper_question_total_score)  into sumNumberPartake,sumUserPartake,maxTotalScore,minTotalScore,avgTotalScore,totalScore from activity_participate where activity_code=activityCode;
    /**计算正确率*/ 
    set accuracy=fullMarks/sumNumberPartake;
    /**要返回的值*/
    select accuracy,sumNumberPartake,sumUserPartake,maxTotalScore,minTotalScore,avgTotalScore,totalScore;
END