缩短SQL server选择计数查询

Question

我对SQL比较陌生，我正在尝试创建一个select查询。这基本上是在不同的条件下重复了几次的表达。有没有办法使代码更有效率，因为我有20多个不同的“条件组”。下面是一个显示两个条件组的示例。

(select count(vps.PrimaryKey) 
   from V_PatentSummarized vps 
            inner join V_TechnologySummarized vts 
                  on vts.PrimaryKey=vps.TechnolFK 
  where (vts.ClientDepartments like '%22%' or vts.ClientDepartments like '%12%') 
    and vps.FileDate between '2015-11-01 00:00:00.000' 
                         and '2016-01-31 00:00:00.000') as Sciences

(select count(vps.PrimaryKey) 
   from V_PatentSummarized vps 
          inner join V_TechnologySummarized vts 
                on vts.PrimaryKey=vps.TechnolFK 
  where (vts.ClientDepartments like '%36%' or vts.ClientDepartments like '%42%') 
    and vps.FileDate between '2015-11-01 00:00:00.000' 
                         and '2016-01-31 00:00:00.000') as Arts

Answer 1

只要所有这些子查询都使用相同的FROM子句，就可以用CASE语句缩短这段时间：

SELECT 
 SUM(CASE WHEN (vts.ClientDepartments like '%22%' or vts.ClientDepartments like '%12%') 
    and vps.FileDate between '2015-11-01 00:00:00.000' 
                         and '2016-01-31 00:00:00.000'
      THEN 1 ELSE 0 END) AS Sciences
 SUM(CASE WHEN (vts.ClientDepartments like '%36%' or vts.ClientDepartments like '%42%') 
    and vps.FileDate between '2015-11-01 00:00:00.000' 
                         and '2016-01-31 00:00:00.000'
      THEN 1 ELSE 0 END) AS Arts
   from V_PatentSummarized vps 
          inner join V_TechnologySummarized vts 
                on vts.PrimaryKey=vps.TechnolFK 

另外，如果所有子查询都具有相同的FileDate范围，则可以将该筛选器向下移到主WHERE子句，而不是将其放在每个表达式中。

Answer 2

尝试拥有一个视图，您还需要区分视图中相同的列

create view vwname
as
select * 
   from V_PatentSummarized vps 
            inner join V_TechnologySummarized vts 
                  on vts.PrimaryKey=vps.TechnolFK 
  where 
     vps.FileDate between '2015-11-01 00:00:00.000' 
                         and '2016-01-31 00:00:00.000'

-现在你可以像

   select count(primarykey) from vwname where clauses