SQL查询：如何从给定的SQL查询中获取所有员工

问题描述：

我有以下查询，我希望所有员工都出现在表中......如何做到这一点？SQL查询：如何从给定的SQL查询中获取所有员工

这是我的表：

SELECT E.EmployeeId --assuming ID is the PK of Employee (E) 
    , tb1.monthDate 
    , ISNULL(present, 0) as present 
    , ISNULL(expected, 0) as expected 
    , ISNULL(late, 0) as late 
FROM (SELECT distinct EmployeeId 
     FROM tblAttendanceDetails) E 
LEFT JOIN (SELECT EmployeeId as id,count(Logintime) as present, month(Logintime) as monthDate 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time)< cast('09:20' as time) 
      GROUP BY EmployeeId,month(Logintime)) as tb1 
    on E.EmployeeId = tb1.id 
LEFT JOIN (SELECT EmployeeId,count(Logintime) as late, month(Logintime) as monthDate2 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time)> cast('09:30' as time) 
      GROUP BY EmployeeId, month(Logintime)) as tb2 
    on E.EmployeeId=tb2.EmployeeId 
and tb1.monthDate=tb2.monthDate2 
LEFT JOIN (SELECT EmployeeId,count(Logintime) as expected,month(Logintime) as monthDate3 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time) between cast('09:20' as time) and cast('09:30' as time) 
      GROUP BY EmployeeId,month(Logintime)) as tb3 
    on E.EmployeeId=tb3.EmployeeId 
and tb1.monthDate=tb3.monthDate3

这是我的查询结果：

您好，欢迎来SO。你将需要提供一些实际的细节，以便我们提供帮助。发送链接到图片不会导致任何人能够帮助。请不要害怕为查询添加一些格式和空白空间，以免它们成为文字墙。这里是开始改善你的问题的好地方。 http://spaghettidba.com/2015/04/24/how-to-post-at-sql-question-on-a-public-forum/ –

你应该重新编辑你的答案并使用图像..它的旁边{}在工具栏中......这样人们可以看到彼此并排。此外，与http://rextester.com/l/sql_server_online_compiler链接发布的内容会再次提高一个数量级。 – JGFMK

您的from子句中的第一个表应该是employee表，并将其所有其他表留给它。由于您可能没有任何出勤细节的员工，您需要一个包含所有员工的源表。此外，子查询的where子句限制那些员工在要求的时间内没有日期;因此您需要所有员工的来源。 – xQbert

答

我设想是这样的......这样的滤波的子查询完成对员工的所有记录没有影响。

SELECT E.id --assuming ID is the PK of Employee (E) 
    , tb1.monthDate 
    , coalesce(present, 0) as present 
    , coalesce(expected, 0) as expected 
    , coalesce(late, 0) as late 
FROM Employee E 
LEFT JOIN (SELECT EmployeeId as id,count(Logintime) as present, month(Logintime) as monthDate 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time)< cast('09:20' as time) 
      GROUP BY EmployeeId,month(Logintime)) as tb1 
    on E.ID = tb1.ID 
LEFT JOIN (SELECT EmployeeId,count(Logintime) as late, month(Logintime) as monthDate2 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time)> cast('09:30' as time) 
      GROUP BY EmployeeId, month(Logintime)) as tb2 
    on E.id=tb2.EmployeeId 
and tb1.monthDate=tb2.monthDate2 
LEFT JOIN (SELECT EmployeeId,count(Logintime) as expected,month(Logintime) as monthDate3 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time) between cast('09:20' as time) and cast('09:30' as time) 
      GROUP BY EmployeeId,month(Logintime)) as tb3 
    on E.id=tb3.EmployeeId 
and tb1.monthDate=tb3.monthDate3

也许你的意思是：

SELECT E.id --assuming ID is the PK of Employee (E) 
    , E.monthDate 
    , coalesce(present, 0) as present 
    , coalesce(expected, 0) as expected 
    , coalesce(late, 0) as late 
FROM (SELECT distinct EmployeeID ID, month(Logintime) as monthdate 
     FROM tblAttendanceDetails) E 
LEFT JOIN (SELECT EmployeeId as id,count(Logintime) as present, month(Logintime) as monthDate 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time)< cast('09:20' as time) 
      GROUP BY EmployeeId,month(Logintime)) as tb1 
    on E.ID = tb1.ID 
and E.Monthdate = t1.monthdate 
LEFT JOIN (SELECT EmployeeId,count(Logintime) as late, month(Logintime) as monthDate2 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time)> cast('09:30' as time) 
      GROUP BY EmployeeId, month(Logintime)) as tb2 
    on E.id=tb2.EmployeeId 
and E.monthDate=tb2.monthDate2 
LEFT JOIN (SELECT EmployeeId,count(Logintime) as expected,month(Logintime) as monthDate3 
      FROM tblAttendanceDetails 
      WHERE cast(Logintime as time) between cast('09:20' as time) and cast('09:30' as time) 
      GROUP BY EmployeeId,month(Logintime)) as tb3 
    on E.id=tb3.EmployeeId 
and E.monthDate=tb3.monthDate3

或者我们可以消除联接和子查询使用窗口功能。

SELECT EmployeeID as ID 
    , month(Logintime) as MonthDate 
    , sum(case when cast(Logintime as time) < cast('09:20' as time) 
       then 1 else 0 end) over (partition by month(Logintime),employeeID) as present 
    , sum(case when cast(Logintime as time)> cast('09:30' as time) 
       then 1 else 0 end) over (partition by month(Logintime),employeeID) as expected 
    , sum(case when cast(Logintime as time) between cast('09:20' as time) and cast('09:30' as time) 
       then 1 else 0 end) over (partition by month(Logintime),employeeID) as late 
FROM tblAttendanceDetails E 
--GROUP BY EmployeeID, month(LoginTime) -- is the group by needed don't think so since we're using the window functions and the case abstracts the logintime to 1/0 that is now summed...but not sure w/o testing.

都能跟得上（约需要由集团关于SQL注释）的基础上：MSFT

对不起，我给出的图像是tblattendanceDetails的图像，还有另一个表tblEmployee包含全部员工 –

对。我想说，如果您希望作为员工的所有员工在查询时没有任何详细信息，您必须使用员工表;因为你说你想要所有的员工，你需要一个包含所有员工的数据集......我很明显错过了一些东西，但是...... – xQbert

好的......当你说：“所有在场的员工”你的意思是tblAttendanceDetails？如果是这样，只需替换我的员工'（从tblAttendanceDetails中选择不同的ID）E' – xQbert

SQL查询：如何从给定的SQL查询中获取所有员工

相关推荐