如何修改此SQL查询以显示最近3个月的结果? [英] How to modify this SQL query to show the results for the last 3 months?

问题描述

我正在开发一个基于培训管理网络的系统，该系统将向管理层显示每个部门的培训记录.我的数据库设计如下:

>员工表:用户名，名称，职位，部门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.

推荐答案

尝试添加一个Having子句.
我已将当前系统日期用作参考.

并非100％肯定会奏效，但您可以尝试一下.

Try by adding a Having clause.
I have used the current system date as reference.

Not 100% sure that this will work but you can try it out.

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个月的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！