双条件求平均值,如何写Sql语句?

qq_39528261 2018-01-29 10:29:25
规则：任教多学科的，应该先学科内平均。再按规则，主科（语数外）乘0.7，副科（其它学科）乘0.3

源数据:
学科 任课老师 教学业绩
语文 科1 40
语文 语2 39.75
语文 语3 36.98
语文 语4 38.44
语文 语1 40
语文 语2 39.75
语文 语3 36.98
数学 数1 40
数学 数2 39.14
数学 数3 39.24
数学 数4 38.17
数学 数5 38.08
数学 数6 38.68
数学 数7 37.75
英语 英1 39.9
英语 英2 39.73
英语 英3 36.69
英语 英4 36.74
英语 英5 40
英语 英6 36.13
英语 英7 36.84
科学 科1 40
科学 科1 38
科学 科1 40
科学 科1 40
科学 科1 37
科学 科1 40
科学 科1 40


以下为要达到的效果:

教师 教学业绩
英6 36.13
英3 36.69
英4 36.74
英7 36.84
语3 36.98
数7 37.75
数5 38.08
数4 38.17
语4 38.44
数6 38.68
数2 39.14
数3 39.24
科1 39.375
英2 39.73
语2 39.75
英1 39.9
数1 40
英5 40
语1 40
...全文
692 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2018-01-29
  • 打赏
  • 举报
 回复
不用太在意给分，给我1分就行了，有1分代表对你有帮助
qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复
两位版主的代码都正确!十分感谢!请问如何评分呢? 我是第一次发帖求助!谢谢
qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复
中国风版主,理解正确,代码正确,结果正确!感谢! 同时感谢二月十六版主!
qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复
中国风 2018-01-29
  • 打赏
  • 举报
 回复
你把#9方法的ORDER BY GETDATE() 去掉可运行
qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复
二月十六版主结果正确,但是代码无法运行!
中国风 2018-01-29
  • 打赏
  • 举报
 回复
引用 12 楼 qq_39528261 的回复:
那要在SQL2012环境才能用，08以前不行
中国风 2018-01-29
  • 打赏
  • 举报
 回复
这样的规则？ 多学科，权重0.7，0.3，单 学科任教不主（主和副）没权重，这样单学科任教有优势，比如只教副 科学，权重直接为1，如果教2个副科 只有0.3
中国风 2018-01-29
  • 打赏
  • 举报
 回复
先平均再合计
改改加上科目数
e.g.
use Tempdb

go

--> --> 中国风(Roy)生成測試數據

 

if not object_id(N'Tempdb..#1') is null

	drop table #1

Go

Create table #1([学科] nvarchar(22),[任课老师] nvarchar(22),[教学业绩] decimal(18,2))

Insert #1

select N'语文',N'科1',40 union all

select N'语文',N'语2',39.75 union all

select N'语文',N'语3',36.98 union all

select N'语文',N'语4',38.44 union all

select N'语文',N'语1',40 union all

select N'语文',N'语2',39.75 union all

select N'语文',N'语3',36.98 union all

select N'数学',N'数1',40 union all

select N'数学',N'数2',39.14 union all

select N'数学',N'数3',39.24 union all

select N'数学',N'数4',38.17 union all

select N'数学',N'数5',38.08 union all

select N'数学',N'数6',38.68 union all

select N'数学',N'数7',37.75 union all

select N'英语',N'英1',39.9 union all

select N'英语',N'英2',39.73 union all

select N'英语',N'英3',36.69 union all

select N'英语',N'英4',36.74 union all

select N'英语',N'英5',40 union all

select N'英语',N'英6',36.13 union all

select N'英语',N'英7',36.84 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',38 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',37 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',40

Go



SELECT [任课老师]



     , CASE WHEN COUNT(*)>1 THEN SUM([教学业绩]* CASE WHEN [学科]IN (N'语文',N'数学',N'英语') THEN 0.7 ELSE 0.3 END ) ELSE SUM([教学业绩]) END AS [教学业绩]



FROM (SELECT [任课老师],[学科],AVG([教学业绩])AS [教学业绩] FROM #1 GROUP BY [任课老师],[学科]) AS t

GROUP BY [任课老师];



/*

任课老师	教学业绩

科1	39.785714

数1	40.000000

数2	39.140000

数3	39.240000

数4	38.170000

数5	38.080000

数6	38.680000

数7	37.750000

英1	39.900000

英2	39.730000

英3	36.690000

英4	36.740000

英5	40.000000

英6	36.130000

英7	36.840000

语1	40.000000

语2	39.750000

语3	36.980000

语4	38.440000

*/
二月十六 2018-01-29
  • 打赏
  • 举报
 回复
这样？
--测试数据

if not object_id(N'Tempdb..#T') is null

    drop table #T

Go

Create table #T([学科] nvarchar(22),[任课老师] nvarchar(22),[教学业绩] decimal(18,8))

Insert #T

select N'语文',N'科1',40 union all

select N'语文',N'语2',39.75 union all

select N'语文',N'语3',36.98 union all

select N'语文',N'语4',38.44 union all

select N'语文',N'语1',40 union all

select N'语文',N'语2',39.75 union all

select N'语文',N'语3',36.98 union all

select N'数学',N'数1',40 union all

select N'数学',N'数2',39.14 union all

select N'数学',N'数3',39.24 union all

select N'数学',N'数4',38.17 union all

select N'数学',N'数5',38.08 union all

select N'数学',N'数6',38.68 union all

select N'数学',N'数7',37.75 union all

select N'英语',N'英1',39.9 union all

select N'英语',N'英2',39.73 union all

select N'英语',N'英3',36.69 union all

select N'英语',N'英4',36.74 union all

select N'英语',N'英5',40 union all

select N'英语',N'英6',36.13 union all

select N'英语',N'英7',36.84 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',38 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',37 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',40

Go

--测试数据结束

SELECT  t.任课老师 ,

        SUM(t.教学业绩 * ( CASE WHEN 学科数量 > 1

                                 AND 学科 IN ( '语文', '数学', '英语' ) THEN 0.7

                            WHEN 学科数量 > 1

                                 AND 学科 NOT IN ( '语文', '数学', '英语' ) THEN 0.3

                            ELSE 1

                       END )) AS 教学业绩

FROM    ( SELECT    AVG(教学业绩) AS 教学业绩 ,

                    COUNT(学科) OVER ( PARTITION BY 任课老师 ORDER BY GETDATE() ) AS 学科数量 ,

                    任课老师 ,

                    学科

          FROM      #T

          GROUP BY  学科 ,

                    任课老师

        ) t

GROUP BY t.任课老师


qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复
规则：任教多学科的，应该先学科内平均。再按规则，主科（语数外）乘0.7，副科（其它学科）乘0.3
qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复


此图可能更直观!
qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复

请版主参考图解!
二月十六 2018-01-29
  • 打赏
  • 举报
 回复
引用 4 楼 qq_39528261 的回复:
感谢版主的帮助,这次科1的数据对了,但是其他的数据不对哦!
应该先学科内平均。再按规则 这个是什么意思？需要按照教学教师求平均值吗？如果只按照学科就求评价，那么后边怎么和教师关联？
qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复
感谢版主的帮助,这次科1的数据对了,但是其他的数据不对哦!
二月十六 2018-01-29
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([学科] nvarchar(22),[任课老师] nvarchar(22),[教学业绩] decimal(18,8))

Insert #T

select N'语文',N'科1',40 union all

select N'语文',N'语2',39.75 union all

select N'语文',N'语3',36.98 union all

select N'语文',N'语4',38.44 union all

select N'语文',N'语1',40 union all

select N'语文',N'语2',39.75 union all

select N'语文',N'语3',36.98 union all

select N'数学',N'数1',40 union all

select N'数学',N'数2',39.14 union all

select N'数学',N'数3',39.24 union all

select N'数学',N'数4',38.17 union all

select N'数学',N'数5',38.08 union all

select N'数学',N'数6',38.68 union all

select N'数学',N'数7',37.75 union all

select N'英语',N'英1',39.9 union all

select N'英语',N'英2',39.73 union all

select N'英语',N'英3',36.69 union all

select N'英语',N'英4',36.74 union all

select N'英语',N'英5',40 union all

select N'英语',N'英6',36.13 union all

select N'英语',N'英7',36.84 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',38 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',37 union all

select N'科学',N'科1',40 union all

select N'科学',N'科1',40

Go

--测试数据结束

SELECT  t.任课老师 ,

        SUM(t.教学业绩 * ( CASE WHEN 学科 IN ( '语文', '数学', '英语' ) THEN 0.7

                        ELSE 0.3

                   END )) AS 教学业绩

FROM    ( SELECT    AVG(教学业绩) AS 教学业绩 ,

                    任课老师 ,

                    学科

          FROM      #T

          GROUP BY  学科 ,

                    任课老师

        ) t

		GROUP BY t.任课老师


qq_39528261 2018-01-29
  • 打赏
  • 举报
 回复
谢谢版主的回复!非常抱歉,提供的模拟结果有误, 按照规则,科1的教学业绩应当是39.7857
中国风 2018-01-29
  • 打赏
  • 举报
 回复
这样效果？ e.g. 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([班级] int,[学科] nvarchar(22),[任课老师] nvarchar(22),[教学业绩] decimal(18,2),[及格率业绩] decimal(18,2),[优生率业绩] decimal(18,2))
Insert #1
select 401,N'语文',N'科1',40,5.00,8.56 union all
select 402,N'语文',N'语2',39.75,5.00,7.00 union all
select 403,N'语文',N'语3',36.98,5.00,4.28 union all
select 404,N'语文',N'语4',38.44,5.00,2.33 union all
select 405,N'语文',N'语1',40,5.00,7.00 union all
select 406,N'语文',N'语2',39.75,5.00,3.11 union all
select 407,N'语文',N'语3',36.98,5.00,2.72 union all
select 401,N'数学',N'数1',40,5.00,8.56 union all
select 402,N'数学',N'数2',39.14,5.00,7.00 union all
select 403,N'数学',N'数3',39.24,5.00,4.28 union all
select 404,N'数学',N'数4',38.17,5.00,2.33 union all
select 405,N'数学',N'数5',38.08,5.00,7.00 union all
select 406,N'数学',N'数6',38.68,5.00,3.11 union all
select 407,N'数学',N'数7',37.75,5.00,2.72 union all
select 401,N'英语',N'英1',39.9,4.98,8.56 union all
select 402,N'英语',N'英2',39.73,5.70,7.00 union all
select 403,N'英语',N'英3',36.69,4.74,4.28 union all
select 404,N'英语',N'英4',36.74,4.87,2.33 union all
select 405,N'英语',N'英5',40,5.33,7.00 union all
select 406,N'英语',N'英6',36.13,4.47,3.11 union all
select 407,N'英语',N'英7',36.84,4.83,2.72 union all
select 401,N'科学',N'科1',40,5.09,8.56 union all
select 402,N'科学',N'科1',38,5.25,7.00 union all
select 403,N'科学',N'科1',40,4.98,4.28 union all
select 404,N'科学',N'科1',40,4.58,2.33 union all
select 405,N'科学',N'科1',37,4.79,7.00 union all
select 406,N'科学',N'科1',40,5.06,3.11 union all
select 407,N'科学',N'科1',40,5.25,2.72
Go 
SELECT [任课老师]
     , COUNT([班级]) AS [任教班数]
     , AVG([教学业绩]) AS [教学业绩]
     , AVG([及格率业绩]) AS [及格率业绩]
     , AVG([优生率业绩]) AS [优生率业绩]
FROM #1
GROUP BY [任课老师];

/*
任课老师	任教班数	教学业绩	及格率业绩	优生率业绩
科1	8	39.375000	5.000000	5.445000
数1	1	40.000000	5.000000	8.560000
数2	1	39.140000	5.000000	7.000000
数3	1	39.240000	5.000000	4.280000
数4	1	38.170000	5.000000	2.330000
数5	1	38.080000	5.000000	7.000000
数6	1	38.680000	5.000000	3.110000
数7	1	37.750000	5.000000	2.720000
英1	1	39.900000	4.980000	8.560000
英2	1	39.730000	5.700000	7.000000
英3	1	36.690000	4.740000	4.280000
英4	1	36.740000	4.870000	2.330000
英5	1	40.000000	5.330000	7.000000
英6	1	36.130000	4.470000	3.110000
英7	1	36.840000	4.830000	2.720000
语1	1	40.000000	5.000000	7.000000
语2	2	39.750000	5.000000	5.055000
语3	2	36.980000	5.000000	3.500000
语4	1	38.440000	5.000000	2.330000
*/
一条SQL语句求前面记录的平均值
一条SQL语句求前面记录的平均值
关于sql 语句求平均值和总和
已知有两张表 部门表department：部门编号 dept_id 、部门名称 dept_name 员工表employee：员工编号 emp_id、员工...1）求各部门的平均工资 select d.dept_name ,round(avg(nvl(e.emp_wage,0)),2) sal from employee
如何解决SQL中求平均值的问题
AVG() ：求列的平均值 SUM(）:求和行的和 COUNT(）:计算行的数目 ROUND(）:结果保留几位小数，例：ROUND(a,2)保留两位小数 下面看一个例题： Table: Prices +---------------+---------+ | Column Name | Type | +-...
sql语句中用select语句查询值大于平均值的情况
现在，我们希望找到 OrderPrice 值高于 OrderPrice 平均值的客户。 我们使用如下 SQL 语句： SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders) 结果集类似这样： ...
python写sql语句_python基础---sql语句
SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言 在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写，表名库名小写； SQL语句可单行或多行书写，以“;”...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章