在SQL中将查询与％结合起来

问题描述：

我一直试图从每个测试中获得类别（关注点）的百分比，但是我在组合这两个查询时遇到了问题。我遇到问题的地方在于划分并得到正确的查询。我用How to calculate percentage with a SQL statement作为参考。我期待通过动态查询获得每个类别（关注）百分比的检查分类。在SQL中将查询与％结合起来

表A

SELECT exams_id as exams, Count(*) AS TotalQuestions 
FROM exams_questions AS eq 
JOIN concerns as con ON eq.concerns_id = con.concerns_id 
GROUP BY exams_id 
ORDER BY exams_id 

+--------------+--------------+ 
| Exams  |TotalQuestions|   
+--------------+--------------+ 
| 1   | 200   | 
| 2   | 100   | 
| 3   | 400   | 
| 4   | 150   | 
+--------------+--------------+

表B

select exams_id as exam, count(con.concerns_id) as numberOfConcern, con.concerns_description, sum(con.concerns_id) as countTotal 
from exams_questions 
join concerns as con on exams_questions.concerns_id = con.concerns_id 
where exams_id is not null 
group by exams_id, con.concerns_id, con.concerns_description 
order by exams_id asc, con.concerns_id 



+----------------+----------------+------------------+ 
| Exams   |ConcernID  | NumberofConcern | 
+----------------+----------------+------------------+ 
| 1   | 1    | 25    | 
| 1   | 5    | 37    | 
| 1   | 33    | 24    | 
| 1   | 43    | 35    | 
| 1   | 44    | 7    | 
| 1   | 45    | 22    | 
| 1   | 46    | 27    | 
| 1   | 47    | 33    | 
| 2   | 1    | 20    | 
| 2   | 4    | 25    | 
| 2   | 22    | 35    | 
| 2   | 24    | 20    | 
+----------------+----------------+------------------+

组合表中

SELECT e.exams_description, eq.exams_id as exams, con.concerns_id as ConcernID, Count(*) as numberofQuestions, Cast(Count(*)* 100.0/Sum(Count(*)) OVER() AS DECIMAL(18, 2)) as ExamPercent 
FROM exams_questions as eq 
JOIN concerns AS con on eq.concerns_id = con.concerns_id 
JOIN exams AS e on e.exams_id = eq.exams_id 
GROUP BY eq.exams_id, con.concerns_id, e.exams_description 
ORDER BY eq.exams_id asc, con.concerns_id 

+-------------------+-----------------+-------------------+---------------+ 
| Exam   | ConcernID  | NumberofConcern | ExamPercent | 
+-------------------+-----------------+-------------------+---------------+ 
| 1    |  1   |  25   | .24   | 
| 1    |  5   |  27   | .26   | 
| 1    |  33   |  24   | .23   | 
| 1    |  43   |  35   | .33   | 
| 1    |  44   |  7    | .07   | 
| 1    |  45   |  22   | .21   | 
| 1    |  46   |  27   | .26   | 
| 1    |  47   |  33   | .31   | 
| 2    |  1   |  20   | .2   | 
| 2    |  4   |  25   | .25   | 
| 2    |  22   |  35   | .35   | 
| 2    |  24   |  20   | .2   | 
+-------------------+-----------------+-------------------+---------------+

，如果有喜欢的考试2.100质询，但问题的数量是静态的这个伟大的工程并且需要随考试一起更改并纳入TotalQuestions。

Cast(count(*)* 100.0/sum(count(*)) over() AS DECIMAL(18, 2)) as ExamPercent

从联合表我相信是变化需要在哪里。

感谢您的任何指导

我仍然试图破译你想问的问题。如果考试1有200个问题，则您的数据库中有8个考试1记录。你想看看什么百分比8是200？ –

答

是不是你要找的？

Select EQ.exams_id as exam, count(con.concerns_id) as numberOfConcern, con.concerns_description, sum(con.concerns_id) as countTotal, tot.TotalQuestions, 
     ROUND(CAST((count(con.concerns_id) * 100.0/tot.TotalQuestions) AS FLOAT), 2) AS ExamPercent 
from exams_questions EQ 
join concerns as con on exams_questions.concerns_id = con.concerns_id 
Join (
      SELECT exams_id as exams, Count(*) AS TotalQuestions 
      FROM exams_questions AS eq 
      JOIN concerns as con ON eq.concerns_id = con.concerns_id 
      GROUP BY exams_id 
    ) tot ON EQ.exams_id = tot.exams_id 
where EQ.exams_id is not null 
group by EQ.exams_id, con.concerns_id, con.concerns_description 
order by EQ.exams_id asc, con.concerns_id

您的第一个查询会计算每个exam_id的总数。因此，我已将它加入到第二个查询中，以获得同一行中每个考试的总计，然后用此总额计算百分比。

在SQL中将查询与％结合起来

相关推荐