如何在所有发送的测验中显示每个部门的总体参与百分比? [英] How to show the overall percentage of participation of each division in all sent quizzes?
问题描述
我是一名新的网络开发人员，我正在开发一个简单的培训管理系统，为用户提供测验.该系统使用ASP.NET和MS SQLServer开发.现在，我需要提出一个查询，显示每个部门在所有提供的安全测验中的参与百分比(这意味着 IsSent = true 的测验).
I am a new web developer and I am developing a simple training management system which provides the users with quizzes. The system developed using ASP.NET and MS SQLServer. Now, I need to come up with a query that shows the percentage of participation of each division in all the offered safety quizzes (which means quizzes with IsSent = true).
我有以下数据库设计:
Employee Table: Username, Name, DivisionCode
Divisions Table: SapCode, Divison
Quiz Table: QuizID, Title, Description, IsSent
UserQuiz: UserQuizID, QuizID, DateTimeComplete, Username
(DivisionCode 是 SapCode 的外键.IsSent 是一个标志，用于指示已提供并发送给用户参与的测验.)
例如，如果我有四个部门；A、B、C 和 D，我已经向这些部门的员工发送了大约 23 个测验.由于每个部门在每个测验中都有特定的参与百分比.我的查询应该显示参与每个部门所有提供的测验的总体百分比.因此，不应针对每个测验对结果进行细分.
For example, If I have four divisions; A, B, C and D, and I have around 23 quizzes have been sent to the employees in those divisions. Since each division has a specific percentage of participation in each quiz. My query should show the overall percentage of participation in all offered quizzes for each division. So the result should not be broken down with respect to each quiz.
那怎么做呢?
这是数据库及其数据的架构:
And here's the schema of the database with its data:
USE [Test]
GO
/****** Object: Table [dbo].[Divisions] Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Divisions](
[SapCode] [nvarchar](50) NOT NULL,
[Division] [varchar](50) NOT NULL,
CONSTRAINT [PK_Divisions] PRIMARY KEY CLUSTERED
(
[SapCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Divisions] ([SapCode], [Division]) VALUES (N'1', N'A')
INSERT [dbo].[Divisions] ([SapCode], [Division]) VALUES (N'2', N'B')
INSERT [dbo].[Divisions] ([SapCode], [Division]) VALUES (N'3', N'C')
/****** Object: Table [dbo].[Quiz] Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Quiz](
[QuizID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) NOT NULL,
[Description] [varchar](50) NOT NULL,
[IsSent] [bit] NOT NULL,
CONSTRAINT [PK_Quiz] PRIMARY KEY CLUSTERED
(
[QuizID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Quiz] ON
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description], [IsSent]) VALUES (1, N'Quiz I', N'Test', 1)
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description], [IsSent]) VALUES (2, N'Quiz II', N'Test test', 1)
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description], [IsSent]) VALUES (3, N'Quiz III', N'TEST TEST', 0)
SET IDENTITY_INSERT [dbo].[Quiz] OFF
/****** Object: Table [dbo].[Employee] Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[Usename] [nvarchar](50) NOT NULL,
[Name] [varchar](50) NOT NULL,
[DivisionCode] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Usename] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'John12', N'John A', N'1')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'John13', N'John B', N'1')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'John15', N'John C', N'3')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Maria12', N'Maria A', N'3')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Rony14', N'Rony A', N'2')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Tid52', N'Tid A', N'3')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Tim12', N'Tim A', N'1')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Tim15', N'Tim B', N'2')
/****** Object: Table [dbo].[UserQuiz] Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserQuiz](
[UserQuizID] [int] IDENTITY(1,1) NOT NULL,
[QuizID] [int] NOT NULL,
[DateTimeComplete] [datetime] NOT NULL,
[Score] [float] NOT NULL,
[Username] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_UserQuiz] PRIMARY KEY CLUSTERED
(
[UserQuizID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[UserQuiz] ON
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (1, 1, CAST(0x0000A07900000000 AS DateTime), 100, N'John12')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (2, 1, CAST(0x0000A07900000000 AS DateTime), 50, N'Tim12')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (3, 1, CAST(0x0000A07B00000000 AS DateTime), 100, N'Rony14')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (4, 1, CAST(0x0000A07900000000 AS DateTime), 0, N'Tim15')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (5, 1, CAST(0x0000A07900000000 AS DateTime), 100, N'Tid52')
SET IDENTITY_INSERT [dbo].[UserQuiz] OFF
/****** Object: ForeignKey [FK_Employee_Divisions] Script Date: 08/03/2012 19:36:09 ******/
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Divisions] FOREIGN KEY([DivisionCode])
REFERENCES [dbo].[Divisions] ([SapCode])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Divisions]
GO
/****** Object: ForeignKey [FK_UserQuiz_Employee] Script Date: 08/03/2012 19:36:09 ******/
ALTER TABLE [dbo].[UserQuiz] WITH CHECK ADD CONSTRAINT [FK_UserQuiz_Employee] FOREIGN KEY([Username])
REFERENCES [dbo].[Employee] ([Usename])
GO
ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_Employee]
GO
/****** Object: ForeignKey [FK_UserQuiz_Quiz] Script Date: 08/03/2012 19:36:09 ******/
ALTER TABLE [dbo].[UserQuiz] WITH CHECK ADD CONSTRAINT [FK_UserQuiz_Quiz] FOREIGN KEY([QuizID])
REFERENCES [dbo].[Quiz] ([QuizID])
GO
ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_Quiz]
GO
更新:结果应该是这样的:
部门*************整体百分比
Division ************* Overall Percentage
A **************** 80
A **************** 80
B **************** 60
B **************** 60
C **************** 50
C **************** 50
总体百分比是每个测验中每个部门的所有完成百分比的总和.例如，测验#1 中分区 A 的完成百分比 = 40%，测验#2 = 60% 和测验#3 = 90%，此查询应显示的总体百分比是所有其中除以发送的测验总数(即 3).我希望我说的很清楚，概念也很明显.
更新 #2:
我仍在为此苦苦挣扎，但我提出了一个查询，该查询显示了每个部门每个测验的员工总数和参与者总数.这是查询:
I am still struggling with it but I came up with a query that shows the total number of employees and participants in each quiz for each division. And here's the query:
SELECT COUNT(dbo.Employee.Usename) AS [Total Number of Employees], COUNT(dbo.UserQuiz.Username) AS [Total Number of Participants], dbo.Divisions.Division,
dbo.Quiz.Title
FROM dbo.Divisions INNER JOIN
dbo.Employee ON dbo.Divisions.SapCode = dbo.Employee.DivisionCode INNER JOIN
dbo.UserQuiz ON dbo.Employee.Usename = dbo.UserQuiz.Username INNER JOIN
dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID
WHERE (dbo.Quiz.IsSent = 1)
GROUP BY dbo.Divisions.Division, dbo.Quiz.Title
我应该怎么做才能改进它并获得我需要的东西?
推荐答案
以下查询可能会满足您的需求.我计算每个部门的员工人数，乘以测验的数量.然后将实际使用的数字除以这个总数:
The following query may do what you want. I calculates the number of employees in each division, multiplies this by the number of quizzes. It then divides the number actually taken by this total:
select de.Division, de.numemployees, de.numemployees*q.numquizzes as qe_combos,
numquizzestaken/1.0*de.numemployees*q.numquizzes as proportion
from (select d.Division, count(*) as numemployees
from Employee e join
Division d
on e.DivisionCode = d.SapCode
group by d.Division
) de left outer join
(select d.Division, count(*) as numquizzestaken
from Employee e join
Division d
on e.DivisionCode = d.SapCode join
UserQuiz uq
on e.UserName = uq.UserName join
Quiz q
on uq.QuidId = q.QuizId and
q.issent = true
group by de.Division
) dq
on de.division = dq.division cross join
(select count(*) as numquizes
from Quiz q
where isSent = true
) q
这篇关于如何在所有发送的测验中显示每个部门的总体参与百分比?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
