结果总结

debugcn 发表于 Dev

马克

我有一个名为questions包含以下数据的表。数据是通过人们的回答收集的。每个问题最多包含 20 个选项。

form      q1      q2        q3      q4        q5     q6  
1         16       1         2       5        11      9
2         9        6         16      5        2       8
3         8        3         2       5        11      9
4         16       1         2       5        11      7
5         16       6         4       5        11      4
6         15       1         2       5        11      3
7         16       1         2       5        11      4
8         15       1         16      5        11      6
.
.

一个人只能回答一次。有超过100000人回答。每个人都必须回答这六个问题。
结果每隔一小时公布一次，所以我必须在每个答案后找出摘要。还有一张桌子，我保留了所有六个问题的正确答案

question      Answer
 1             16
 2              1
 3              2
 4              5
 5              11 
 6              9

现在我希望我的输出在第一个结果后如下所示：

1 / 6 (1 correct answer out of 6 )= 4

在第二个结果之后：

2 / 6 (1 correct answer out of 6 )= 3
1 / 6 (1 correct answer out of 6 )= 3

第三个结果后：

3 / 6 (1 correct answer out of 6 )= 3
2 / 6 (1 correct answer out of 6 )= 1 
1 / 6 (1 correct answer out of 6 )= 3 
.
.so on till sixth result
.

在第六个结果之后：

6/ 6 (1 correct answer out of 6 )= 1
5 / 6 (1 correct answer out of 6 )= 2 
4 / 6 (1 correct answer out of 6 )= some value
3 / 6 (1 correct answer out of 6 )= some value
2 / 6 (1 correct answer out of 6 )= some value 
1 / 6 (1 correct answer out of 6 )= some value

我希望结果按降序排列。如果没有匹配，则显示 0。

嘘

我认为您可以使用这样的查询：

;with unpivoted as (
    select form, 1 question, q1 answer from questions q
    union all
    select form, 2 question, q2 answer from questions q
    union all
    select form, 3 question, q3 answer from questions q
    union all
    select form, 4 question, q4 answer from questions q
    union all
    select form, 5 question, q5 answer from questions q
    union all
    select form, 6 question, q6 answer from questions q
), results as (
    select u.form, count(ca.Answer) corrects
    from unpivoted u
    left join correctAnswers ca
      on u.question = ca.question and u.answer = ca.Answer
    group by u.form
)
select cast(coalesce(u.question, r.corrects) as varchar(7)) + ' / 6' correctsPer6, count(distinct r.form) countOfForms
from unpivoted u
full outer join results r on u.question = r.corrects
group by u.question, r.corrects
order by u.question desc;

[SQL Fiddle Demo]

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。