求一存储过程

Question

表 题库表
题号id   提行    填写者    打分者
1         1       甲         x1
2         1       甲         x2
3         2       甲         x1
4         2       甲         x2
5         2       甲         x3
10        3       甲         x2
12        4       甲         x1
15        1       乙         x1
18        1       乙         x2
19        2       乙         x3
20        1       丙         x1表成绩表
分数id   题号id   分数    月份
1          1        1      6月
2          2        1      6月
3          3        1      6月
4          4        1      6月
5          5        1      6月
6          10       1      6月
7          12       1      6月
8          15       1      6月
9          18       1      6月
10         19       1      6月
11         20       1      6月
 要求得到如下结果集
姓名     分数
甲        4
乙        2
丙        1分数得出方法：题行一样的取平均，然后相加，得到一个人的总分，如甲：第一行有两个人打分，取平均为1，甲第二行有三个人打分取平均为1分，甲第三行与第四行都是一个人打分，两个1分，1234行相加为4分。同理乙得1+1为2分，丙的1分，然后出个人总成绩进行排序，得到所要结果集

Answer 1

--> 测试数据：@题库表
declare @题库表 table([题号id] int,[提行] int,[填写者] varchar(2),[打分者] varchar(2))
insert @题库表
select 1,1,'甲','x1' union all
select 2,1,'甲','x2' union all
select 3,2,'甲','x1' union all
select 4,2,'甲','x2' union all
select 5,2,'甲','x3' union all
select 10,3,'甲','x2' union all
select 12,4,'甲','x1' union all
select 15,1,'乙','x1' union all
select 18,1,'乙','x2' union all
select 19,2,'乙','x3' union all
select 20,1,'丙','x1'
--> 测试数据：@成绩表
declare @成绩表 table([分数id] int,[题号id] int,[分数] int,[月份] varchar(3))
insert @成绩表
select 1,1,1,'6月' union all
select 2,2,1,'6月' union all
select 3,3,1,'6月' union all
select 4,4,1,'6月' union all
select 5,5,1,'6月' union all
select 6,10,1,'6月' union all
select 7,12,1,'6月' union all
select 8,15,1,'6月' union all
select 9,18,1,'6月' union all
select 10,19,1,'6月' union all
select 11,20,1,'6月'--select * from @题库表
--select * from @成绩表
--select T .填写者 as 姓名, SUM(su/Ct)  as 分数 from (
select a.填写者, a.提行,COUNT( a.打分者) as Ct,SUM( b.分数) as su  from @题库表 a,@成绩表 b  where a.题号id= b.题号id
 group by a.填写者, a.提行  )T  group by T.填写者 order by 分数 desc
---------------------------
姓名 分数
甲 4
乙 2
丙 1

Answer 2

select 填写者,分数=sum(分数)
from(
select 填写者,提行,分数=avg(分数)
from [题库表] a,[成绩表] b
where a.题号id=b.题号id
group by 填写者,提行
 )t
group by 填写者
order by 分数 desc填写者  分数
---- -----------
甲    4
乙    2
丙    1(3 行受影响)

Answer 3

select t.[填写者] 姓名,sum([分数]) 分数 
from 
(
select [填写者],avg(分数) [分数]
from @题库表 a inner join @成绩表 b
on a.[题号id]=b.[题号id]
group by [填写者],提行
) t group by [填写者]
order by [分数] DESC

Answer 4

SELECT 姓名, SUM(分数) FROM (SELECT A.填写者 AS 姓名, AVG(B.分数) AS 分数
FROM ITEM A
LEFT JOIN RESULT B ON A.题号id = B.题号id
GROUP BY A.填写者, A.提行) t
GROUP BY 姓名