问题描述
我正在开发一个基于培训管理网络的系统,该系统将向管理层显示每个部门的培训记录.我的数据库设计如下:
>员工表:用户名,名称,职位,部门ID
>部门表:部门ID,部门名称
>测验表:QuizID,标题,说明
> UserQuiz表:UserQuizID,得分,DateTimeComplete,QuizID,用户名
**注意:**每个表中的第一个属性是主键.
我用于此任务的SQL查询是:
I am developing a training management web-based system which will show the management the training record for each division. My database design is like following:
> Employee Table: Username, Name, Job, DivisionID
> Division Table: DivisionID, DivisionName
> Quiz Table: QuizID, Title, Description
> UserQuiz Table: UserQuizID, Score, DateTimeComplete, QuizID, Username
**NOTE:** The first attribute in each table is the primary key.
The SQL Query that I am using for this task is:
SELECT COUNT(DISTINCT dbo.UserQuiz.QuizID) AS [Total Number of Quizzes], dbo.Divisions.DivisionName, DATENAME(Month, dbo.UserQuiz.DateTimeComplete)
AS Month FROM dbo.UserQuiz INNER JOIN
dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID INNER JOIN
dbo.employee ON dbo.UserQuiz.Username = dbo.employee.Username RIGHT OUTER JOIN
dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode GROUP BY dbo.Divisions.DivisionName, DATENAME(Month, dbo.UserQuiz.DateTimeComplete)
该查询将向我显示每个部门基于月的测验总数.我现在要显示的是过去三个月的这些结果.另外,即使有不进行任何测验的部门,我也想显示所有部门.这意味着我想显示零个测验题.
This query will show me the total number of taken quizzes by each division based on month. What I want now is showing these results for the last three months. Also, I want to show all the divisions even if there is a division which does not take any quiz. This means I want to show the division with zero number of taken quizzes.
推荐答案
SELECT COUNT(DISTINCT dbo.UserQuiz.QuizID) AS [Total Number of Quizzes], dbo.Divisions.DivisionName, DATENAME(Month, dbo.UserQuiz.DateTimeComplete)
AS Month FROM dbo.UserQuiz INNER JOIN
dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID INNER JOIN
dbo.employee ON dbo.UserQuiz.Username = dbo.employee.Username RIGHT OUTER JOIN
dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode GROUP BY dbo.Divisions.DivisionName, DATENAME(Month, dbo.UserQuiz.DateTimeComplete) HAVING (Month(GETDATE()) - Month (dbo.UserQuiz.DateTimeComplete)) > 3
这篇关于如何修改此SQL查询以显示最近3个月的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!