sql统计一个字段多个逗号分隔的字符或Id数量

  1、  String sql = " SELECT SUM(LENGTH(ee)-LENGTH(REPLACE(ee,',','')))AS  pointCount FROM (SELECT DISTINCT le.`lesson_perp_exercise_id`, ex.`point_info`AS ee FROM lesson_exercise_answer_info AS le  " +
                        "LEFT JOIN lesson_perp_exercise_info AS ex ON ex.id=le.lesson_perp_exercise_id " +
                        "LEFT JOIN lesson_info_bean AS lb ON   le.lesson_id=lb.id "+" WHERE lb.class_id= '"+ classId
                        + "' " + " and le.create_time like'%" + string + "%') as nn";

SUM(LENGTH(ee)-LENGTH(REPLACE(ee,',','')))可以统计出来 一个字段中存在多个用逗号分隔的字符或者Id   直接统计出来字符或者Id的数量。

 

2、@Query("SELECT new map(SUM(CASE WHEN INSTR(s.answer,'A')>0 THEN 1 ELSE 0 END)AS acount,SUM(CASE WHEN INSTR(s.answer,'B')>0 THEN 1 ELSE 0 END)AS bcount,SUM(CASE WHEN INSTR(s.answer,'C')>0  THEN 1 ELSE 0 END)AS ccount,SUM(CASE WHEN INSTR(s.answer,'D')>0  THEN 1 ELSE 0 END)AS dcount,SUM(CASE WHEN INSTR(s.answer,'E')>0  THEN 1 ELSE 0 END)AS ecount,SUM(CASE WHEN INSTR(s.answer,'F')>0  THEN 1 ELSE 0 END)AS fcount ) FROM LessonExerciseAnswerInfoBean as s where s.lessonPerpExerciseId=?1 and s.lessonId=?2 and s.idDelete=0 ")

new map(SUM(CASE WHEN INSTR(s.answer,'A')>0 THEN 1 ELSE 0 END)AS acount统计出来整张表中单个字段选项的数量