如何显示每个部门参与所有测验的总体情况? [英] How to show the overall of participation of each division in all offered quizzes?
本文介绍了如何显示每个部门参与所有测验的总体情况?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我是一名新的Web开发人员，正在开发一个简单的培训管理系统，该系统可为用户提供测验.该系统使用ASP.NET和SQLServer开发.
我具有以下数据库设计:
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 SQLServer.
I have the following database design:
Employee Table:
Username, Name, DivisionCode
Divisions Table:
SapCode, Divison
Quiz Table:
QuizID, Title, Description, IsSent
UserQuiz:
UserQuizID, QuizID, DateTimeComplete, Username
(DivisionCode是SapCode的外键.IsSent是一个标志，用于指示已提供并发送给用户参加的测验.)
我需要提出一个查询，该查询显示每个部门在所有提供的安全测验中的参与百分比(这意味着IsSent = true的测验).
例如，如果我有四个部门； A，B，C和D，以及我大约有23项测验已发送给这些部门的员工.由于每个部门在每个测验中都有特定的参与百分比.我的查询应显示每个部门所有测验中的总参与百分比.因此，不应针对每个测验细分结果.
那怎么办?
这是数据库模式:
(DivisionCode is a foreign key to the SapCode. IsSent is a flag to indicate to the quizzes that have been offered and sent to the users to participate in them.)
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).
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.
So how to do that?
And here''s the database schema:
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
推荐答案
您尝试了什么?我认为这是不可能的，因为已在测验级别而不是在雇员级别存在.还是DateTimeComplete你怎么知道?您正在使用字符串作为外键吗?为什么没有员工编号?您需要计算部门代码的员工人数，然后需要计算部门代码相同时通过用户名加入员工的用户测验，并计算出％.我认为这是非常简单的SQL.
What have you tried ? I don''t think this is possible, given that issent exists on the quiz level, and not on the employee level. Or is DateTimeComplete how you tell ? You''re using a string as a foreign key ? Why is there no employee ID ? you need to do a count of employees for a division code, then you need to do a count of userquiz joined to employee by username where the division code is the same, and work out the %. It''s pretty straightforward SQL, I think.
这篇关于如何显示每个部门参与所有测验的总体情况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
