SQL Server中滚动计数的递归CTE透视？,

f0ofjuux 于 2023-02-11 发布在 SQL Server

关注(0)|答案(1)|浏览(145)

基本上，我有一个成员资格日期和状态变化表，我想创建一个滚动计数的每一个成员数量的状态为每个日期一样：

我想输出如下：

不太确定构造这个查询的最佳方法，我想我必须创建一个递归查询来计算该日期的事件，然后透视输出来为成员状态创建单独的列？
短暂性脑缺血发作

create table MyTable(status int,st datetime, memberID int)
insert into MyTable values(1,'2022-02-01',1),
(1,'2022-03-01' ,2),
(2,'2022-04-01',1)

select [st],[1],[2]
from (select [st],memberID,status from MyTable) as SrcTable
pivot(
count(memberID) for status in ([1],[2])
) as pivottable
order by [st] asc

但是它并没有像我希望的那样在三月份聚集起来

sql-server

来源：https://stackoverflow.com/questions/75376281/recursive-cte-pivot-for-rolling-count-in-sql-server

1条答案

按热度按时间

bqjvbblv1#

我把最后一个日期设置为get_date（），因为这是我在现实生活中遇到的问题所需要的，但我确实使用了递归CTE和透视

create table MyTable(status int,st datetime, memberID int)
insert into MyTable values(1,'2022-02-01',1),
(1,'2022-03-01' ,2),
(2,'2022-04-01',1);

with cte as (
 select status,st,memberID,(case when Nxt_sts is null then 0 else Nxt_sts end) 
 as Nxt_sts,
(case when Nxt_st is null then  FORMAT(DateAdd(Month,1,getdate()), 'yyyyMM01') 
else Nxt_st end) as Nxt_st
from (select
A.status,A.st,A.memberID,B.status as Nxt_sts,B.st as nxt_st
from MyTable A
left join MyTable B
 on A.MemberID = B.MemberID
 and A.status+1 = B.status ) A
)

, cte_2 as 
(
select status,st,memberID,Nxt_sts,Nxt_st
from cte
Union All
select status,DateAdd(Month, 1 ,st),memberID,Nxt_sts,Nxt_st from cte_2
where DateAdd(Month, 1 ,st) < Nxt_st
 )

select [st],[1],[2],[3]
from (select [st],memberID,status from cte_2) as SrcTable
pivot(
count(memberID) for status in ([1],[2],[3])
) as pivottable
order by [st] asc

赞(0）回复(0）举报 2023-02-11

我来回答

SQL Server中滚动计数的递归CTE透视？,

1条答案

相关问题

热门标签

最新问答