34,623
社区成员
![](https://csdnimg.cn/release/cmsfe/public/img/topic.427195d5.png)
![](https://csdnimg.cn/release/cmsfe/public/img/me.40a70ab0.png)
![](https://csdnimg.cn/release/cmsfe/public/img/task.87b52881.png)
![](https://csdnimg.cn/release/cmsfe/public/img/share-circle.3e0b7822.png)
比如A表,字段B为日期,字段C为人,字段D为买东西。请问怎么写代码达到下面效果。
第一天A买东西,统计为一次,第二天A又买了,统计为两次。第三天A没有买,统计为0.第四天买了,统计为一次。只要断了就重新计数。
【字节SQL面试真题拆解,地狱级难度,保姆级教程—连续登录问题的求解
】https://www.bilibili.com/video/BV1mQ4y1r7Kh/?spm_id_from=333.337.search-card.all.click&vd_source=32daa2743a817af5135ab88d7123ce16
【统计sql中连续出现的次数】https://blog.csdn.net/qq_37056683/article/details/112008505?ops_request_misc=&request_id=&biz_id=102&utm_term=sql%20%E8%BF%9E%E7%BB%AD%E5%87%BA%E7%8E%B0%E6%AC%A1%E6%95%B0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-2-112008505.142^v88^control_2,239^v2^insert_chatgpt&spm=1018.2226.3001.4187
如果楼主考虑每个日期都要有数的话,可以利用master.dbo.spt_values 获得连续的日期,然后left join实现
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([B] Date,[C] nvarchar(21),[D] nvarchar(22))
Insert #T
select '2023-03-10',N'A',N'香蕉' union all
select '2023-03-11',N'A',N'香蕉' union all
select '2023-03-12',N'A',N'香蕉' union all
select '2023-03-14',N'A',N'香蕉' union ALL
select '2023-03-15',N'A',N'香蕉' union all
select '2023-03-01',N'B',N'香蕉' union all
select '2023-03-02',N'B',N'香蕉' union all
select '2023-03-15',N'B',N'香蕉'
Go
--测试数据结束
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY C ORDER BY B) AS rn from #T
),cteb AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY C,DAY(B)-rn ORDER BY B) AS rn1 FROM cte
)
SELECT B,C,cteb.D,rn1 AS 次数 FROM cteb
做两层窗口函数:
select *, sum(1) over(partition by name, cumsum order by date) rownum
from
(select *, sum(tag) over(partition by name order by date) cumsum
from
(SELECT *,
case when date = lag(date) over(partition by name order by date) + 1
then 0 else 1 end tag
FROM test) t ) t
```sql