本文介绍了按ms sql server中的列和行值求和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张包含以下数据的表。
Hi,
I have a table with following data.
Id CorrectOption OptionAnswered RelationId
1 2 4 10
2 1 1 10
3 1 2 10
4 3 3 10
5 4 4 10
这里我需要与CorrectOption列进行比较OptionAnswered列。
我需要输出60%正确(3个选项正确)。
谢谢
Suresh
Here I need to compare with CorrectOption column with OptionAnswered column.
I need output as 60% correct (3 options correct).
Thanks
Suresh
推荐答案
DECLARE @tmp TABLE (Id INT IDENTITY(1,1), CorrectOption INT, OptionAnswered INT, RelationId INT)
INSERT INTO @tmp (CorrectOption, OptionAnswered, RelationId)
VALUES(2, 4, 10), (1, 1, 10), (1, 2, 10), (3, 3, 10), (4, 4, 10)
SELECT RelationId, ProperAnswers, CountOfAnswers, CONVERT(INT, (CONVERT(DECIMAL(8,2), ProperAnswers)/CONVERT(DECIMAL(8,2),CountOfAnswers))*100) + '%' AS [%]
FROM (
SELECT t1.RelationId, COUNT(t1.RelationId) As CountOfAnswers, t2.ProperAnswers
FROM @tmp AS t1 INNER JOIN (
SELECT RelationId, COUNT(RelationId) AS ProperAnswers
FROM @tmp
WHERE CorrectOption = OptionAnswered
GROUP BY RelationId
) AS t2 ON t1.RelationId = t2.RelationId
GROUP BY t1.RelationId, t2.ProperAnswers
) AS T
结果:
Result:
Rel.. PropA CountA %
10 3 5 60%
这篇关于按ms sql server中的列和行值求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!