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

avatar 2019年9月21日12:43:38 评论 686 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

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: