如何修改这个SQL查询? [英] How to modify this sql query?

问题描述

我在ASP.NET网站上开发了一个与此类似的测验引擎，但是我只是在数据库设计中添加了一个新表.数据库的原始设计包含三个表，如下所示:

I developed a quiz engine similar to this one in the ASP.NET website, but I just added a new table to the design of the database. The original design of the database consists of three tables as following:

Quiz Table: QuizID, Title, Description
Question Table: QuestionID, Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer, AnswerExplanation, QuestionOrder, QuizID
UserQuiz Table: UserQuizID, QuizID, DateTimeComplete, Score, Username

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

我所做的只是添加了一些表:
用户表:用户名，名称，JobTitle，DivisionCode(用户名是此表的主键)
部门表:部门代码，部门名称

我现在正在开发一个仪表板，该仪表板应在表中包含一些图表和统计信息.为此，在这个强大社区的帮助下，我想到了一些功能强大的查询，这些查询正在处理原始数据库设计.现在，我需要修改这些查询.这个查询让我很苦恼:

The first attribute in each table is the primary key of that table

What I did is just adding a few tables which are:
User Table: Username, Name, JobTitle, DivisionCode (Username is the primary key of this table)
Divisions Table: DivisionCode, DivisionName

I am developing now a dashboard that should contain some charts and statistics in table. For that, with a help from this great community I came up with some powerful queries that was working on the original database design. Now, I need to modify these queries. I am struggled a lot with this query:

SELECT
 (SELECT COUNT(DISTINCT Q.UserName)
 FROM dbo.UserQuiz Q
 WHERE DateTimeComplete >= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as ParticipantsLast7Days,
 (SELECT COUNT(Q.QuizID)
 FROM dbo.UserQuiz Q
 WHERE Q.DateTimeComplete >= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as QuizzesLast7Days,
 (SELECT COUNT(DISTINCT Q.UserName)
 FROM dbo.UserQuiz Q
 WHERE DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as ParticipantsLast30Days,
 (SELECT COUNT(Q.QuizID)
 FROM dbo.UserQuiz Q
 WHERE Q.DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
 as QuizzesLast30Days

如您所见，它将显示上周和上个月参加的测验的总数，以及上周和上个月参加的测验的总数.

**现在，使用数据库的当前(新)设计，它应该向我显示上周和上个月每个部门中已参加测验的总数以及所有测验中的参与者总数.另外，如果它能显示每个部门的员工总数并显示完全没有参加任何测验的员工人数，那将是很棒的**

问题是我不知道如何修改它以获取这些最后的东西.

As you see, it will show me the total number of taken quizzes last week and last month, and the total number of participants last week and last month.

**Now, with the current (new) design of the database, it should show me the total number of taken quizzes and the total number of participants in all quizzes in each division on last week and last month. Plus, it will be great if it shows the total number of employees in each division with displaying the number of employees who did not participate at all in any quiz**

The problem is I don''t know how to modify it to get these last things.

推荐答案

尝试一下:

try this:

;WITH cteTemp 
AS
(
SELECT Username
FROM dbo.Quiz Q
where Q.DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
group by q.Username
)
SELECT COUNT(u.Username)
FROM UserTable U	 
left outer join cteTemp c
ON c.Username=u.Username
where c.UsernameIS NULL

这篇关于如何修改这个SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！