一条sql求每周的总播放量

[weixin_39014156]

如：
时间 平台 播放量
2017-6-1 1 20
2017-6-1 2 30
2017-6-1 3 20
2017-6-1 4 50

2017-6-2 1 20
2017-6-2 2 30
2017-6-2 4 50

2017-6-3 1 20
2017-6-3 2 30
2017-6-3 3 20


2017-6-4 1 20
2017-6-4 2 30
2017-6-4 3 20
2017-6-4 4 50
..................

要求 求每周的总播放量=各平台播放量相加
比如第一周=第一周所有平台的总和
第二周=前两周各平台最大值的总和
第三周=前三周各平台最大值的总和
............

[二月十六]

想要什么样的结果？

SET DATEFIRST 1
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([时间] Date,[平台] int,[播放量] int)
Insert #T
select '2017-6-1',1,20 union all
select '2017-6-1',2,30 union all
select '2017-6-1',3,20 union all
select '2017-6-1',4,50 union all
select '2017-6-2',1,20 union all
select '2017-6-2',2,30 union all
select '2017-6-2',4,50 union all
select '2017-6-3',1,20 union all
select '2017-6-3',2,30 union all
select '2017-6-3',3,20 union all
select '2017-6-4',1,20 union all
select '2017-6-4',2,30 union all
select '2017-6-4',3,20 union all
select '2017-6-4',4,50 union all
select '2017-6-9',4,50
Go
--测试数据结束
;WITH cte AS (
SELECT  DATEPART(WEEK, [时间]) AS [时间] ,
        SUM([播放量]) AS [播放量]
FROM    #T
GROUP BY DATEPART(WEEK, [时间])
)
SELECT  a.时间 ,
        SUM(b.播放量) AS 播放量
FROM    cte a
        JOIN cte b ON a.时间 >= b.时间
GROUP BY a.时间

[weixin_39014156]

引用 1 楼 sinat_28984567 的回复:

想要什么样的结果？

SET DATEFIRST 1
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([时间] Date,[平台] int,[播放量] int)
Insert #T
select '2017-6-1',1,20 union all
select '2017-6-1',2,30 union all
select '2017-6-1',3,20 union all
select '2017-6-1',4,50 union all
select '2017-6-2',1,20 union all
select '2017-6-2',2,30 union all
select '2017-6-2',4,50 union all
select '2017-6-3',1,20 union all
select '2017-6-3',2,30 union all
select '2017-6-3',3,20 union all
select '2017-6-4',1,20 union all
select '2017-6-4',2,30 union all
select '2017-6-4',3,20 union all
select '2017-6-4',4,50 union all
select '2017-6-9',4,50
Go
--测试数据结束
;WITH cte AS (
SELECT  DATEPART(WEEK, [时间]) AS [时间] ,
        SUM([播放量]) AS [播放量]
FROM    #T
GROUP BY DATEPART(WEEK, [时间])
)
SELECT  a.时间 ,
        SUM(b.播放量) AS 播放量
FROM    cte a
        JOIN cte b ON a.时间 >= b.时间
GROUP BY a.时间

大神不是我要的结果 我要的是： 比如 第三周=前三周里 每个平台的最大值的和 第四周=前四周里 每个平台的最大值的和

[二月十六]

引用 2 楼 weixin_39014156 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 想要什么样的结果？

SET DATEFIRST 1
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([时间] Date,[平台] int,[播放量] int)
Insert #T
select '2017-6-1',1,20 union all
select '2017-6-1',2,30 union all
select '2017-6-1',3,20 union all
select '2017-6-1',4,50 union all
select '2017-6-2',1,20 union all
select '2017-6-2',2,30 union all
select '2017-6-2',4,50 union all
select '2017-6-3',1,20 union all
select '2017-6-3',2,30 union all
select '2017-6-3',3,20 union all
select '2017-6-4',1,20 union all
select '2017-6-4',2,30 union all
select '2017-6-4',3,20 union all
select '2017-6-4',4,50 union all
select '2017-6-9',4,50
Go
--测试数据结束
;WITH cte AS (
SELECT  DATEPART(WEEK, [时间]) AS [时间] ,
        SUM([播放量]) AS [播放量]
FROM    #T
GROUP BY DATEPART(WEEK, [时间])
)
SELECT  a.时间 ,
        SUM(b.播放量) AS 播放量
FROM    cte a
        JOIN cte b ON a.时间 >= b.时间
GROUP BY a.时间

大神不是我要的结果 我要的是： 比如 第三周=前三周里 每个平台的最大值的和 第四周=前四周里 每个平台的最大值的和[/quote] 第一周是所有平台的和，然后以后的周是前几周每个平台最大值的和？

[weixin_39014156]

引用 3 楼 sinat_28984567 的回复:

[quote=引用 2 楼 weixin_39014156 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 想要什么样的结果？

SET DATEFIRST 1
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([时间] Date,[平台] int,[播放量] int)
Insert #T
select '2017-6-1',1,20 union all
select '2017-6-1',2,30 union all
select '2017-6-1',3,20 union all
select '2017-6-1',4,50 union all
select '2017-6-2',1,20 union all
select '2017-6-2',2,30 union all
select '2017-6-2',4,50 union all
select '2017-6-3',1,20 union all
select '2017-6-3',2,30 union all
select '2017-6-3',3,20 union all
select '2017-6-4',1,20 union all
select '2017-6-4',2,30 union all
select '2017-6-4',3,20 union all
select '2017-6-4',4,50 union all
select '2017-6-9',4,50
Go
--测试数据结束
;WITH cte AS (
SELECT  DATEPART(WEEK, [时间]) AS [时间] ,
        SUM([播放量]) AS [播放量]
FROM    #T
GROUP BY DATEPART(WEEK, [时间])
)
SELECT  a.时间 ,
        SUM(b.播放量) AS 播放量
FROM    cte a
        JOIN cte b ON a.时间 >= b.时间
GROUP BY a.时间

大神不是我要的结果 我要的是： 比如 第三周=前三周里 每个平台的最大值的和 第四周=前四周里 每个平台的最大值的和[/quote] 第一周是所有平台的和，然后以后的周是前几周每个平台最大值的和？[/quote] 是的

[二月十六]

SET DATEFIRST 1
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([时间] Date,[平台] int,[播放量] int)
Insert #T
select '2017-6-1',1,20 union all
select '2017-6-1',2,30 union all
select '2017-6-1',3,20 union all
select '2017-6-1',4,50 union all
select '2017-6-2',1,20 union all
select '2017-6-2',2,30 union all
select '2017-6-2',4,50 union all
select '2017-6-3',1,20 union all
select '2017-6-3',2,30 union all
select '2017-6-3',3,20 union all
select '2017-6-4',1,20 union all
select '2017-6-4',2,30 union all
select '2017-6-4',3,20 union all
select '2017-6-4',4,50 union all
select '2017-6-9',1,90 union all
select '2017-6-9',4,100
Go
--测试数据结束
;WITH cte AS (
SELECT  DATEPART(WEEK, [时间]) AS [时间] ,
        [平台] ,
        MAX([播放量]) AS [播放量]
FROM    #T
GROUP BY DATEPART(WEEK, [时间]) ,
        [平台]
),ctea AS (
SELECT  a.时间 ,
        b.平台 ,
        MAX(b.播放量) AS 播放量
FROM    cte a
        JOIN cte b ON a.时间 >= b.时间
GROUP BY a.时间 ,
        b.平台
)
SELECT  时间 ,
        SUM(播放量) AS 播放量
FROM    ctea
GROUP BY 时间
ORDER BY 时间