CREATE FUNCTION earlyTable(
@startTime datetime,@endTime datetime
)
RETURNS table
AS
RETURN
(
select wrecord.EmpID,wrecord.EmpName,early= case when count(earlyleave ) is null then 0
else count(earlyleave ) end
from wrecord
where sysdate between @startTime and endTime如果count(earlyleave )没有记录的时候,把他设置为0,求问应该怎么修改?
@startTime datetime,@endTime datetime
)
RETURNS table
AS
RETURN
(
select wrecord.EmpID,wrecord.EmpName,early= case when count(earlyleave ) is null then 0
else count(earlyleave ) end
from wrecord
where sysdate between @startTime and endTime如果count(earlyleave )没有记录的时候,把他设置为0,求问应该怎么修改?
(2)没有group by 吗
else count(earlyleave ) end -->isnull(count(earlyleave),0)
else count(earlyleave ) end
from wrecord
where sysdate between @startTime and endTime 首先,感觉你这个语句是有问题的1、有count 怎么能没有 group by 呢
2、在有group by 的前提下,count为0 这种情况是不存在的,至少是1
字段1 字段2 0
@startTime datetime,@endTime datetime
)
RETURNS table
AS
RETURN
(
select wrecord.EmpID,wrecord.EmpName,early= count(earlyleave )
from wrecord
where sysdate between @startTime and @endTime
and earlyleave='Y'
group by wrecord.EmpID,wrecord.EmpName这个是原来的函数,如果count是0的话,一点记录也显示不出来,我想要的是,如果count是0,就显示
empID empName early
1 a 0
这个是原来的函数,如果count是0的话,一点记录也显示不出来,我想要的是,如果count是0,就显示
empID empName early
1 a 0
都没有行数,
EMPID还有值?
因为有这条语句,导致我所有的记录全被过滤掉了,所以count不到记录,才会出来空记录的情况,求问有没有人知道怎么解决这个问题?
出来
empID empName early
1 a 0
这个结果
SELECT ISNULL((SELECT COUNT(*) FROM TB),0)