sql - 如何在 SQL Server 2008 中的以下查询中保留临时表？

Question

select Executive, count([Cat A]) AS [1-3], count([Cat B]) AS [4-6],count([Cat C]) AS [7-10],count([Cat D]) AS [11-15],count([Cat E]) AS [16+]
      from (
            select 
                  Executive,
                case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 0 and DATEDIFF(d,[next follow up date],getdate()) < 4)
                              then 'A'
                  end as [Cat A],
                  case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 3 and DATEDIFF(d,[next follow up date],getdate()) < 7)
                              then 'B'
                  end as [Cat B],
                  case when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 6 and DATEDIFF(d,[next follow up date],getdate()) < 11)
                              then 'C'
                  end as [Cat C],
                  case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 10 and DATEDIFF(d,[next follow up date],getdate()) < 16)
                              then 'D'
                  end as [Cat D],
                  case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 15)
                              then 'E'
                  end as [Cat E]
                  from vw_FollowUps)
            as Table1
      group by Executive, [Cat A], [Cat B], [Cat C], [Cat D], [Cat E]

输出如下

Executive  1-3  4-6  7-10  11-15  16+

Rani       0    0     0     0     0
Rani       0    1     0     2     0
Rani       0    0     1     0     0 

但我需要像下面这样的输出

Executive  1-3  4-6  7-10  11-15  16+

Rani        0    1     1     2     0

Answer 1

在整个事物周围添加一个选择:

SELECT Executive  SUM([1-3]), SUM([4-6]), SUM([7-10]), SUM([11-15]), SUM([16+])
FROM
(

   ....YOUR BIG SELECT....
)
GROUP BY Executive

所以像这样:

SELECT Executive  SUM([1-3]), SUM([4-6]), SUM([7-10]), SUM([11-15]), SUM([16+])
FROM
(

    select Executive, count([Cat A]) AS [1-3], count([Cat B]) AS [4-6],count([Cat C]) AS [7-10],count([Cat D]) AS [11-15],count([Cat E]) AS [16+]
      from (
            select 
                  Executive,
                case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 0 and DATEDIFF(d,[next follow up date],getdate()) < 4)
                              then 'A'
                  end as [Cat A],
                  case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 3 and DATEDIFF(d,[next follow up date],getdate()) < 7)
                              then 'B'
                  end as [Cat B],
                  case when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 6 and DATEDIFF(d,[next follow up date],getdate()) < 11)
                              then 'C'
                  end as [Cat C],
                  case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 10 and DATEDIFF(d,[next follow up date],getdate()) < 16)
                              then 'D'
                  end as [Cat D],
                  case  when [Next Follow Up Date] < GETDATE() and (DATEDIFF(d,[next follow up date],getdate()) > 15)
                              then 'E'
                  end as [Cat E]
                  from vw_FollowUps)
            as Table1
      group by Executive, [Cat A], [Cat B], [Cat C], [Cat D], [Cat E]

)
GROUP BY Executive