sql - Sql 删除子查询中的空值

Question

我正在尝试进行子选择查询，但我的组中有 Null 值

SELECT        convert(varchar, dbo.ArretProductionJournee.DateArret, 3) , 
(select
sum (datediff(minute, ArretProductionJournee.HeureDebut, ArretProductionJournee.HeureFin)  ) 
where ArretProductionJournee.EnumArret Like 'HH')
as HH, 
(select
sum (datediff(minute, ArretProductionJournee.HeureDebut, ArretProductionJournee.HeureFin)  ) 
where ArretProductionJournee.EnumArret Like 'HI')
as HI,
(select
sum (datediff(minute, ArretProductionJournee.HeureDebut, ArretProductionJournee.HeureFin)  ) 
where ArretProductionJournee.EnumArret Like 'PS')
as PS
FROM            
dbo.ArretProductionJournee 
where dbo.ArretProductionJournee.DateArret BETWEEN '01/04/2021'and '03/04/2021'
group by ArretProductionJournee.EnumArret, convert(varchar, dbo.ArretProductionJournee.DateArret, 3)

结果如下：

我想删除那些 Null 值以获得如下结果：

---------------------------
Date Arrêt | HH | HI | PS |
---------------------------
03 / 02/ 21| 0  | 29 | 45 |

标签： sqlsql-servernullsubquerysql-subselect

Answer 1

我很确定你只想要条件聚合：

select convert(date, dbo.ArretProductionJournee.DateArret), 
       sum(case when apj.EnumArret = 'HH'
                then datediff(minute, apj.HeureDebut, apj.HeureFin)  
           end) as HH, 
       sum(case when apj.EnumArret = 'HI'
                then datediff(minute, apj.HeureDebut, apj.HeureFin)  
           end) as HI, 
       sum(case when apj.EnumArret = 'PS'
                then datediff(minute, apj.HeureDebut, apj.HeureFin)  
           end) as PS
from  dbo.ArretProductionJournee apj
where apj.DateArret between '2021-04-01' and '2021-04-03'
group by convert(date, apj.DateArret);