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

avatar 2019年09月21日12:43:38 6 23912 views
博主分享免费Java教学视频，B站账号：Java刘哥 ，长期提供技术问题解决、项目定制：本站商品点此
最近帮一个朋友写一个打分系统，就是他手下有一批讲师，讲师讲完课后，学员要对讲师打分，对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
  22.     -- 获得今天的总分和平均分
  23.     LEFT JOIN (
  24.     SELECT
  25.         teacher_id,
  26.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) AS total,
  27.         sum( q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 ) / count( * ) AS avg
  28.     FROM
  29.         sens_record
  30.     WHERE
  31.         to_days( create_time ) = to_days( now( ) )
  32.     GROUP BY
  33.         teacher_id
  34.     ) AS today ON t.id = today.teacher_id
  36. WHERE t.type = 1
  37. ORDER BY total DESC

结果如下

历史上的今天
九月
21日
  • 微信
  • 交流学习，资料分享
  • weinxin
  • 个人淘宝
  • 店铺名：言曌博客咨询部

  • (部分商品未及时上架淘宝)
avatar

发表评论

avatar 登录者：匿名
匿名评论，评论回复后会有邮件通知

  

已通过评论：0   待审核评论数：0