子查询返回多个值.当子查询遵循SQL Server时，不允许这样做

Question

我有这个SQL过程:

@Month int
as
begin
select COUNT(*) as absence,employeeId from(select MONTh,DAY,employeeId,(select COUNT(*) 
from employee_L lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) as leaveID
from employee_c c,holiday hl,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' AS datetime))+@Month-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+@Month-1, -1)) 
where DATENAME(DD, Weekday) IN (select dayId+1 from days) and (select lv.EmployeeId 
from employee_L lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) is NULL and Date not between hl.startDate and hl.endDate and c.isActive=1 )sc group by employeeId
end

当我选择3作为月参数时，我得到一个错误:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

如何解决这个错误??

注意:对于参数3,employee_L有多于一行(3行)

两个查询出现错误:

(select lv.EmployeeId 
from employee_L lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) is NULL 

和

select COUNT(*) 
from employee_L lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) as leaveID

更具体地说，我不会使用TOP 1或count(*)，我只需要从这个查询中获得所有结果:

select MONTH,DAY,employeeId,(select lv.EmployeeId 
from employee_l lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) from employee_c c,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' AS datetime))+2-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+2-1, -1))

表Employee_lv的模式

Employee_lvId         start_day         end_day           days          
0415             2021-03-02       2021-03-01          3                         
0785             2021-04-01       2021-04-10          10 

如果我只有一行，我只得到所有月份的天数，不包括Employee_l中存在的天数(例如，如果我选择3作为month "March")

结果预期:

(只有一行，假设只有第一行)=>31-3 = 28我将得到28行为空

但是如果我有两行，我没有得到我需要的。我得到了第一个问题中描述的错误!!在这种情况下我能做什么?

Answer 1

看起来你在等待

and (select lv.EmployeeId 
from employee_L lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) is null

只返回单行

应该是

and not exists (
select * 
from employee_L lv 
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1
)

Answer 2

正如我从您的评论中理解的那样，您希望在COUNT(*)和NULL检查中进行相同的查询。因此，您可以修改NULL检查，如下所示。

(SELECT lv.EmployeeId
FROM employee_L lv
WHERE DATE BETWEEN lv.start_date
AND lv.end_date
AND lv.EmployeeId = c.employeeId
AND c.isActive = 1) IS NULL 

你可以做的是:

(select COUNT(*) 
from employee_L lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) = 0