在SQL选择查询方面遇到麻烦

Question

我正在为某些计数编写一个选择语句。

这样：

Select JobType, count(JobType) as count from PrintJobs with (nolock)
where JobType in (1, 2, 3, 4)
and convert(datetime, TimeSubmitted) > cast(CAST(dateadd(dd, -1,GETDATE()) as date)  as datetime) + CAST('17:30:00.000' as time)
group by JobType

问题在于，当没有出现Jobtypes的情况时，表看起来像

JobTypes | count  |
------------------|
    2        1    |
    3        2    |
__________________|

是否有这样做的方法：

JobTypes | count  |
------------------|
    1        0    |
    2        1    |
    3        2    |
    4        0    | 
__________________|

因此，即使结果中没有任何结果，它仍然显示出来？我正在尝试使其尽可能动态，在可以在C＃SQL参数中设置Jobtypes的地方

谢谢

Answer 1

一个解决方案是 left join：

Select j.JobType, count(pj.JobType) as count
from (values (1), (2), (3), (4)) j(jobtype) left join
     PrintJobs pj
     on pj.JobType = j.jobType and
        convert(datetime, TimeSubmitted) > cast(CAST(dateadd(day, -1,GETDATE()) as date)  as datetime) + CAST('17:30:00.000' as time)
group by j.JobType

Answer 2

在SQL Server中这样：

Select JTS.JobType, count(*) as count 
from (
   SELECT 1 as JT
     UNION ALL
   SELECT 2 as JT
     UNION ALL
   SELECT 3 as JT
     UNION ALL
   SELECT 4 as JT
) AS JTS
LEFT JOIN PrintJobs AS P with (nolock) ON P.JobType = JTS.JT 
      AND convert(datetime, P.TimeSubmitted) > cast(CAST(dateadd(dd, -1,GETDATE()) as date)  as datetime) + CAST('17:30:00.000' as time)
group by P.JobType

在这里，您可以创建一个类型表并加入。

Answer 3

  Select JTypes.JType, count(P.JobType) as count 
 from (
 SELECT 1 as JType
 UNION ALL
 SELECT 2 as JType
 UNION ALL
 SELECT 3 as JType
 UNION ALL
 SELECT 4 as JType
  ) AS JTypes
  LEFT JOIN PrintJobs AS P ON P.JobType = JTypes.JType 
   WHERE  convert(datetime, P.TimeSubmitted) > cast(CAST(dateadd(dd, -1,GETDATE()) as date)  as datetime) + CAST('17:30:00.000' as time)
group by JTypes.JType