MySQL多列求和、求平均值,统计求总数/平均数和今日总数/平均数

avatar 2019年09月21日18:22:40 0 3337 views
最近帮一个朋友写一个打分系统,就是他手下有一批讲师,讲师讲完课后,学员要对讲师打分,对10个问题打1-5分。然后后台统计所有讲师的总得分,总平均分,今日得分,今日平均分情况,并排名。 本着一条SQL语句解决的办法,我们可以写出下面的SQL语句 我们先看打分记录表,目前我们设计得很简单,10个问题,对应10个字段(因为对方需求很简单,且不常变,如果经常变,建议弄一个关联表) 我们可以对上面的需求进行分解 1、统计所有讲师的总分
  1. SELECT
  2.         teacher_id,
  3.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) AS total
  4. FROM
  5.     sens_record
  6. GROUP BY
  7.     teacher_id
  2、统计所有讲师的平均分
  1. SELECT
  2.       teacher_id,
  3.       sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) / count( * ) AS avg
  4. FROM
  5.     sens_record
  6. GROUP BY
  7.     teacher_id
  3、统计今天的数据 只需要 WHERE 后面加以下条件即可
  1. to_days( create_time ) = to_days( now( ) )
  4、如果为 NULL 就设置为 0 可以用
  1. IFNULL( 字段, 0 )
  5、查询所有的讲师,然后左连接添加今日所有讲师得分
  1. SELECT
  2.     t.id,
  3.     t.NAME,
  4.     IFNULL( today.total, 0 ) AS todayTotal
  5. FROM
  6.     sens_teacher t
  7. LEFT JOIN (
  8.     SELECT
  9.         teacher_id,
  10.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) AS total
  11.     FROM
  12.         sens_record
  13.     WHERE
  14.         to_days( create_time ) = to_days( now( ) )
  15.     GROUP BY
  16.         teacher_id
  17.     ) AS today
  18. ON t.id = today.teacher_id
    最后完整的 统计所有讲师的总得分,总平均分,今日得分,今日平均分情况,并排名
  1. SELECT
  2.     t.id,
  3.     t.NAME,
  4.     IFNULL( total.total, 0 ) AS total,
  5.     IFNULL( ROUND(total.avg), 0 ) AS avg,
  6.     IFNULL( today.total, 0 ) AS todayTotal,
  7.     IFNULL( ROUND(today.avg), 0 ) AS todayAvg
  8. FROM
  9.     sens_teacher t
  10.     -- 获得所有的总分和平均分
  11.     LEFT JOIN (
  12.     SELECT
  13.         teacher_id,
  14.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) AS total,
  15.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) / count( * ) AS avg
  16.     FROM
  17.         sens_record
  18.     GROUP BY
  19.         teacher_id
  20.     ) AS total ON t.id = total.teacher_id
  21.     -- 获得今天的总分和平均分
  22.     LEFT JOIN (
  23.     SELECT
  24.         teacher_id,
  25.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) AS total,
  26.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) / count( * ) AS avg
  27.     FROM
  28.         sens_record
  29.     WHERE
  30.         to_days( create_time ) = to_days( now( ) )
  31.     GROUP BY
  32.         teacher_id
  33.     ) AS today ON t.id = today.teacher_id
  34. WHERE t.type = 1
  35. ORDER BY total DESC
结果如下
  • 微信
  • 交流学习,有偿服务
  • weinxin
  • 博客/Java交流群
  • 资源分享,问题解决,技术交流。群号:590480292
  • weinxin
avatar
已通过评论:0   待审核评论数:0