如何显示上次发送/参加的测验中的参与者人数? [英] How to show the number of participants in the last sent/taken quiz?

问题描述

我正在开发一个简单的Quiz Engine，类似于ASP.NET网站中的那个.我具有以下数据库设计:

用户表:用户名，名称，部门代码...等

分区表:SapCode，分区

测验表:QuizID，标题，已发送，说明

UserQuiz:UserQuizID，QuizID，DateTimeComplete，得分，用户名

(第一个属性是每个表中的主键)

每周，我公司的员工都会收到一封电子邮件通知，以参加系统中可用的新测验.将发送给员工的测验将具有(True)作为IsSent属性的值，这就是将其与未发送给员工的其他测验区分开来的原因.我现在要显示的是最近一次测验的参与者人数，因为每周只发送一次测验.


我的查询显示所有已参加测验的参与者总数:

I am developing a simple Quiz Engine similar to that one in the ASP.NET website. I have the following database design:

User Table: Username, Name, DivisionCode... etc

Division Table: SapCode, Division

Quiz Table: QuizID, Title, IsSent, Description

UserQuiz: UserQuizID, QuizID, DateTimeComplete, Score, Username

(The first attribute is the primary key in each table)

Every week, the employees in my company will receive an email notification to participate in the new quiz that is available in the system. The quiz that will be sent to the employees will have (True) as a value of IsSent attribute and this is what discriminates it from the other quizzes which are not being sent to the employees. What I want now is showing the number of participants in the last taken quiz since there is only one quiz will be sent each week.


My Query that shows the total number of participants in all taken quizzes:

SELECT     dbo.Divisions.DivisionShortcut, COUNT(DISTINCT dbo.UserQuiz.Username) AS [Number of Participants], dbo.Quiz.QuizID
    FROM         dbo.Divisions INNER JOIN
                          dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode INNER JOIN
                          dbo.UserQuiz ON dbo.employee.Username = dbo.UserQuiz.Username INNER JOIN
                          dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID
    WHERE     (dbo.Quiz.IsSent = 1)
    GROUP BY dbo.Divisions.DivisionShortcut, dbo.Quiz.QuizID

那么如何解决它以仅显示上一次参加的测验(已发送的测验)中的参与者人数?

So how to fix it to show only the number of participants in the last taken quiz (the quiz that is sent)?

推荐答案

添加WHERE子句中的另一个条件，并根据UserQuiz.DateTimeComplete列限制结果.

Add another condition in the WHERE clause and restrict he results based on UserQuiz.DateTimeComplete column.

这篇关于如何显示上次发送/参加的测验中的参与者人数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！