如何在Server 2008中将临时表保存在下面的查询中？
如何在Server 2008中将临时表保存在下面的查询中？
提问于 2013-04-23 04:09:46
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回答 4
Stack Overflow用户
发布于 2013-04-23 04:14:42
在整个过程中添加一个选择：
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 ExecutiveStack Overflow用户
发布于 2013-04-23 04:18:34
另外一个选择(另外两个答案)是无关的。
仅按Executive列进行简单分组。
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;Stack Overflow用户
发布于 2013-04-23 04:21:04
您可以使用Common Table Expression(CTE)来超越CTE，result...You可以使用CTE进行如下新的查询：(没有测试，但实现的方法是正确的)
WITH CTETABLE AS
(
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]
)
SELECT Executive, MAX([1-3]),MAX([4-6]),MAX([7-10]),MAX([11-15],MAX([16+]) FROM CTETABLE GROUP BY Executive;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16160783
复制相关文章
相似问题
腾讯云开发者
