sql - 如果 sql select except 语句的结果没有返回记录，如何获取值 0？

Question

我的 SQL except 语句有问题，在我的例子中，我选择了两列，一列可以有值，而其他列不能有，因为第一个 SQL 语句的计数等于 except 语句之后的第二个 SQL 的计数。

我得到的结果：

GroupId      absences

   1            3

预期结果

  GroupId      absences
     1             3
     2             0

在这种情况下我该怎么办？

select
    groupId,
    (case when groupId = null then 0 else COUNT(*) end) as absence
from (
    select
        groupId,
        COUNT(*) as  absences1
    from (
        select distinct
            MONTh,
            DAY,
            e.groupId
        from employee_c c,
            holiday hl,
            employee e,
            groups,
            Get_Calendar_Date(
                DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime)) + @Month - 1, 0),
                DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime)) + @Month-1, -1)
            )
        where DATENAME(DD, Weekday) IN (
            select WorkingdayId+1
            from timetable s,
                groups ds,
                grouptime de
            where dd.groupId = ds.groupId
                and dd.groupId = de.groupId
                and s.timetableId = de.timetableId
                and de.groupId = ds.groupdId)
                and DATEPART(MM,hl.startDate) = @Month
                and c.isActive=1 
        except
       (select Month,
               Day,
               d.departmentId
        from department d,
             Get_Calendar_Date(
                    DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime)) + @Month - 1, 0),
                    DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime)) + @Month - 1, -1)
             ),
             holiday hle,
             employee_c c,
             employee e
        where datepart(MM,hle.startDate) = @Month
                and cast(Date as date) between hle.startDate and hle.endDate
                and c.isActive=1
                and d.groupId =e.groupId
                and c.employeeId=e.employeeId
                and c.isActive=1
        )
    ) sc
    group by Month,Day,groupId
) s
group by groupId

标签： sqlsql-serverexcept