如何使用sql server 2008在子查询中使用Select Count [英] How can I use a Select Count in a subquery using sql server 2008

问题描述

我正在尝试从2个表Employee和Attendance中显示员工详细信息列表（id，code，name，timein，timeOut，NumberOfWorkedDays）：我尝试此查询但是它给出了错误的消息子查询返回的值超过1（工作日数应在特定月份）

  SELECT  MST_EMPLOYEE。[ID]  as  ID 
，[EMPCODE] 
 
，[EMPFNAME] + ' ' + [EMPMNAME] + ' ' + [EMPLNAME]  AS  EMPNAME 
，（ SELECT  DESIGNATION  FROM  MST_DESIGNATION  WHERE  ID = 
（ SELECT   TOP （ 1 ）DESIGNATIONID < span class =code-keyword> FROM  EMP_PROFESSIONALDETAILS  WHERE  EMPID = MST_EMPLOYEE.ID  ORDER   BY  HIREDATE  DESC ）） AS  DESIGNATION 
，min（Times_IN） as  TimeIn，max（Times_Out） as  TimeOut，
 （ select  count（ distinct  _Date）来自 Emp_Attendance 其中月（_date）= 6  group   by  EmpID）
 
 
  FROM  [MST_EMPLOYEE]  inner  < span class =code-keyword> join  Emp_Attendance   Emp_Attendance.EmpID = MST_Employee.ID 
  where  month（Times_IN）=  6  
 
  group   by  MST_Employee.ID，MST_Employee.EmpCode，EmpFName，EmpMName，EmpLName 

请问有什么解决方案吗？

解决方案

为什么不使用另一个连接而不是子查询

  SELECT  MST_EMPLOYEE。[ID]  as  ID 
，[EMPCODE]， [EMPFNAME] + ' ' + [EMPMNAME] + '  ' + [EMPLNAME]  AS  EMPNAME 
，DESIGNATION 
， min（Times_IN） as  TimeIn，max（Times_Out） as  TimeOut，
（< span class =code-keyword> select  count（ distinct  _Date）来自 Emp_Attendance < span class =code-keyword>其中月（_date）= 6  group   by  EmpID）
  FROM  [MST_EMPLOYEE] 内部  join  Emp_Attendance   Emp_Attendance.EmpID = MST_Employee.ID 
  INNER   JOIN  EMP_PROFESSIONALDETAILS  ON  MST_EMPLOYEE.ID = EMP_PROFESSIONALDETAILS.EMPID 
  INNER   JOIN  MST_DESIGNATION  ON  MST_DESIGNATION.ID = EMP_PROFESSIONALDETAILS.DESIGNATIONID 
  where  month（Times_IN）=  6  
  group   by  MST_Employee.ID，MST_Employee.EmpCode，EmpFName，EmpMName，EmpLName， DESIGNATION 

I'm trying to display the list of employee details (id, code, name, timein, timeOut, NumberOfWorkedDays) from 2 tables "Employee" and "Attendance": I try this query but it gives a wrong message "the subquery returned more than 1 value" (the number of worked days should be in a specific month)

SELECT  MST_EMPLOYEE.[ID] as ID
      ,[EMPCODE]

      ,[EMPFNAME]+' '+[EMPMNAME]+' '+[EMPLNAME] AS EMPNAME
     ,(SELECT DESIGNATION FROM MST_DESIGNATION WHERE ID=
     (SELECT TOP(1) DESIGNATIONID FROM EMP_PROFESSIONALDETAILS WHERE EMPID=MST_EMPLOYEE.ID ORDER BY HIREDATE DESC)) AS DESIGNATION
    , min(Times_IN) as TimeIn , max(Times_Out) as TimeOut,
    (select count (distinct _Date) from Emp_Attendance  where  month (_date)=6 group by EmpID)


  FROM [MST_EMPLOYEE] inner join Emp_Attendance on Emp_Attendance.EmpID=MST_Employee.ID
 where month(Times_IN) = 6

 group by MST_Employee.ID, MST_Employee.EmpCode, EmpFName, EmpMName, EmpLName

Any solution please ?

解决方案

Why not using another join instead of Sub Query

SELECT  MST_EMPLOYEE.[ID] as ID
      ,[EMPCODE],[EMPFNAME]+' '+[EMPMNAME]+' '+[EMPLNAME] AS EMPNAME
     ,DESIGNATION
    , min(Times_IN) as TimeIn , max(Times_Out) as TimeOut,
    (select count (distinct _Date) from Emp_Attendance  where  month (_date)=6 group by EmpID)
  FROM [MST_EMPLOYEE] inner join Emp_Attendance on Emp_Attendance.EmpID=MST_Employee.ID
INNER JOIN EMP_PROFESSIONALDETAILS ON MST_EMPLOYEE.ID=EMP_PROFESSIONALDETAILS.EMPID
INNER JOIN MST_DESIGNATION ON MST_DESIGNATION.ID=EMP_PROFESSIONALDETAILS.DESIGNATIONID
 where month(Times_IN) = 6
 group by MST_Employee.ID, MST_Employee.EmpCode, EmpFName, EmpMName, EmpLName,DESIGNATION

这篇关于如何使用sql server 2008在子查询中使用Select Count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！