如何显示每个部门每天访问系统的员工人数

Question

我是使用C#的ASP.NET的新开发人员。我为其中一家公司开发了一个基于网络的应用程序，现在我需要开发一个日志，显示访问该系统的唯一人数。例如，AA部门的员工A和B今天访问了该系统。员工A在同一天访问系统两次。日志应该显示，今天有两名AA部门的员工访问了该系统，而不是三名。

我有以下数据库设计：

Employee Table: Username, Name, DivisionCode (DivisionCode is a foreign key to SapCode)
Divisions Table: SapCode DivisionShortcut   
Log Table: ID, Username, DateTimeAccessing

我编写了以下查询，显示用户名、员工姓名、部门和DateTimeAccessing：

SELECT     dbo.[Log].Username, dbo.employee.Name, dbo.Divisions.DivisionShortcut, dbo.[Log].DateTimeAccessing
FROM         dbo.employee INNER JOIN
dbo.[Log] ON dbo.employee.Username = dbo.[Log].Username INNER JOIN
dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode

查询应根据部门和日期显示每天访问系统的员工人数，例如：

At Nove 21, Division AA = 2    
Nove 21, Division BB = 7    
Nove 20, Division AA = 12 and so on

那么该怎么做呢

*更新：*我现在使用以下查询：

SELECT     CONVERT(varchar(10), l.DateTimeAccessing, 120) AS Date, d.DivisionShortcut, COUNT(DISTINCT l.Username) AS Cnt
FROM         dbo.employee AS e INNER JOIN
dbo.[Log] AS l ON e.Username = l.Username RIGHT OUTER JOIN
dbo.Divisions AS d ON e.DivisionCode = d.SapCode
GROUP BY CONVERT(varchar(10), l.DateTimeAccessing, 120), d.DivisionShortcut

现在的问题是，例如，如果我想查看今天或11月10日所有部门的统计数据，那么日期列将显示当天没有员工访问系统的部门的NULL值为什么？如何修复

Answer 1

使用count(distinct)计算用户数，使用group by根据除数和日期对结果进行分组。

您可以使用convert从datetime中获取日期作为字符串，但要注意，这会降低查询速度。

select d.DivisionShortcut, convert(varchar(10),l.DateTimeAccessing,120), count(distinct l.Username) as Cnt
from dbo.employee e
inner join dbo.[Log] l on e.Username = l.Username
inner join dbo.Divisions d on e.DivisionCode = d.SapCode
group by d.DivisionShortcut, convert(varchar(10),l.DateTimeAccessing,120)

更新：

以下是如何加入日期：

select d.DivisionShortcut, t.DateAccessing, count(distinct l.Username) as Cnt
from dbo.employee e
inner join dbo.[Log] l on e.Username = l.Username
inner join dbo.Divisions d on e.DivisionCode = d.SapCode
inner join (
select distinct convert(varchar(10),DateTimeAccessing,120) as DateAccessing
from dbo.Divisions
) t on t.DateAccessing = convert(varchar(10),l.DateTimeAccessing,120)
group by d.DivisionShortcut, t.DateAccessing

Answer 2

试试这个

SELECT  cast(L.DateTimeAccessing as DATE) DateTimeAccessing
,D.DivisionShortcut
,count(distinct L.Username) as Cnt
FROM dbo.employee E
INNER JOIN dbo.[Log] L on E.Username = L.Username
INNER JOIN dbo.Divisions D on E.DivisionCode = D.SapCode
GROUP BY cast(L.DateTimeAccessing as DATE),D.DivisionShortcut