34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT xzq,YEAR(b.tm) AS y,sum(a.s1+a.s2) AS [sum]
FROM a INNER JOIN b ON a.idA=b.idA
GROUP BY xzq,YEAR(b.tm)
)
SELECT t.*,isnull(tt.sumMax,0) AS sumMax ,isnull(tt.sumMin,0) AS sumMin
,isnull(tt.sumAVG,0) as sumAVG, t.[sum]-isnull(tt.sumAVG,0) as ssAT FROM t
OUTER APPLY (SELECT MAX(it.[sum]) AS sumMax,Min(it.[sum]) AS sumMin,avg(CONVERT(FLOAT, isnull(it.[sum],0))) AS sumAvg FROM t AS it WHERE it.y<t.y AND it.xzq=t.xzq) tt
ORDER BY t.xzq,t.y desc
/*
xzq y sum sumMax sumMin sumAVG ssAT
hn 2016 30 23 13 18.6666666666667 11.3333333333333
hn 2015 20 23 13 18 2
hn 2014 23 13 13 13 10
hn 2013 13 0 0 0 13
yn 2016 15 11 10 10.5 4.5
yn 2015 10 11 11 11 -1
yn 2013 11 0 0 0 11
*/
谢谢啊![/quote]
[/quote]
with a(idA,xzq,s1, s2) AS (
select 1,'hn',4, 6 UNION ALL
select 2,'hn' ,6,7 UNION ALL
select 3,'hn',3, 4 UNION ALL
select 4,'hn', 2,1 UNION ALL
select 5, 'hn',8,1 UNION ALL
select 6, 'hn' , 7 , 5 UNION ALL
select 7,'hn', 5, 8 UNION ALL
select 8,'hn', 3 , 8 UNION ALL
select 9,'hn', 6 , 2 UNION ALL
select 10,'yn' , 3 , 2 UNION ALL
select 11 , 'yn', 7 ,3 UNION ALL
select 12,'yn', 6, 5 UNION ALL
select 13,'yn' ,8 , 2
),b(id ,idA,tm) AS (
select 4 ,1,'2016-02-01' UNION ALL
select 5,2,'2016-03-01' UNION ALL
select 6,3,'2016-05-01' UNION ALL
select 7,4,'2015-02-01' UNION ALL
select 8, 5 ,'2015-03-01' UNION ALL
select 9 ,6,'2014-05-01' UNION ALL
select 10,7,'2013-02-01' UNION ALL
select 11,8,'2014-06-01' UNION ALL
select 12,9,'2015-07-01' UNION ALL
select 13,10,'2016-03-01' UNION ALL
select 14, 11,'2015-05-01' UNION ALL
select 15,12,'2013-04-01' UNION ALL
select 16,13,'2016-08-01'
),t AS (
SELECT xzq,YEAR(b.tm) AS y,sum(a.s1+a.s2) AS [sum]
FROM a INNER JOIN b ON a.idA=b.idA
GROUP BY xzq,YEAR(b.tm)
)
SELECT t.*,isnull(tt.sumMax,0) AS sumMax ,isnull(tt.sumMin,0) AS sumMin
,isnull(tt.sumAVG,0) as sumAVG, t.[sum]-isnull(tt.sumAVG,0) as ssAT FROM t
OUTER APPLY (SELECT MAX(it.[sum]) AS sumMax,Min(it.[sum]) AS sumMin,avg(isnull(it.[sum],0)) AS sumAvg FROM t AS it WHERE it.y<t.y AND it.xzq=t.xzq) tt
ORDER BY t.xzq,t.y desc
/*
xzq y sum sumMax sumMin sumAVG ssAT
hn 2016 30 23 13 18 12
hn 2015 20 23 13 18 2
hn 2014 23 13 13 13 10
hn 2013 13 0 0 0 13
yn 2016 15 11 10 10 5
yn 2015 10 11 11 11 -1
yn 2013 11 0 0 0 11
*/
谢谢啊![/quote]
with a(idA,xzq,s1, s2) AS (
select 1,'hn',4, 6 UNION ALL
select 2,'hn' ,6,7 UNION ALL
select 3,'hn',3, 4 UNION ALL
select 4,'hn', 2,1 UNION ALL
select 5, 'hn',8,1 UNION ALL
select 6, 'hn' , 7 , 5 UNION ALL
select 7,'hn', 5, 8 UNION ALL
select 8,'hn', 3 , 8 UNION ALL
select 9,'hn', 6 , 2 UNION ALL
select 10,'yn' , 3 , 2 UNION ALL
select 11 , 'yn', 7 ,3 UNION ALL
select 12,'yn', 6, 5 UNION ALL
select 13,'yn' ,8 , 2
),b(id ,idA,tm) AS (
select 4 ,1,'2016-02-01' UNION ALL
select 5,2,'2016-03-01' UNION ALL
select 6,3,'2016-05-01' UNION ALL
select 7,4,'2015-02-01' UNION ALL
select 7,4,'2015-02-01' UNION ALL
select 7,4,'2015-02-01' UNION ALL
select 7,4,'2015-02-01' UNION ALL
select 7,4,'2015-02-01' UNION ALL
select 8, 5 ,'2015-03-01' UNION ALL
select 7,4,'2015-02-01' UNION ALL
select 9 ,6,'2014-05-01' UNION ALL
select 10,7,'2013-02-01' UNION ALL
select 11,8,'2014-06-01' UNION ALL
select 12,9,'2015-07-01' UNION ALL
select 13,10,'2016-03-01' UNION ALL
select 14, 11,'2015-05-01' UNION ALL
select 15,12,'2013-04-01' UNION ALL
select 16,13,'2016-08-01'
),t AS (
SELECT xzq,YEAR(b.tm) AS y,sum(a.s1+a.s2) AS [sum]
FROM a INNER JOIN b ON a.idA=b.idA
GROUP BY xzq,YEAR(b.tm)
)
SELECT t.*,tt.* , t.[sum]-isnull(tt.sumAVG,0)FROM t
OUTER APPLY (SELECT MAX(it.[sum]) AS sumMax,Min(it.[sum]) AS sumMin,avg(it.[sum]) AS sumAvg FROM t AS it WHERE it.y<t.y AND it.xzq=t.xzq) tt
ORDER BY t.xzq,t.y desc
/*
xzq y sum sumMax sumMin sumAvg AT
hn 2016 30 35 13 23 7
hn 2015 35 23 13 18 17
hn 2014 23 13 13 13 10
hn 2013 13 NULL NULL NULL 13
yn 2016 15 11 10 10 5
yn 2015 10 11 11 11 -1
yn 2013 11 NULL NULL NULL 11
*/
with A(idA,xzq,s1,s2) as
(
select 1,'hn',4,6 union
select 2,'hn',6,7 union
select 3,'hn',3,4 union
select 4,'hn',2,1 union
select 5,'hn',8,1 union
select 6,'hn',7,5 union
select 7,'hn',5,8 union
select 8,'hn',3,8 union
select 9,'hn',6,2 union
select 10,'yn',3,2 union
select 11,'yn',7,3 union
select 12,'yn',6,5 union
select 13,'yn',8,2
),B(id,idA,tm) AS
(
select 4,1,'2016-02-01' union
select 5,2,'2016-03-01' union
select 6,3,'2016-05-01' union
select 7,4,'2015-02-01' union
select 8,5,'2015-03-01' union
select 9,6,'2014-05-01' union
select 10,7,'2013-02-01' union
select 11,8,'2014-06-01' union
select 12,9,'2015-07-01' union
select 13,10,'2016-03-01' union
select 14,11,'2015-05-01' union
select 15,12,'2013-04-01' union
select 16,13,'2016-08-01'
)
select A.xzq
,tm=year(b.tm)
,s1Adds2=sum(A.s1+A.s2)
,sumMax=MAX(A.s1+A.s2)
,sumMin=min(A.s1+A.s2)
,sumAvg=avg(A.s1+A.s2)
--,ssAT=sum(A.s1+A.s2)-avg(A.s1+A.s2)
from A
inner join B
on A.idA=B.idA
group by A.xzq,YEAR(B.tm)
order by a.xzq,YEAR(B.tm) desc
/*
xzq tm s1Adds2 sumMax sumMin sumAvg
hn 2016 30 13 7 10
hn 2015 20 9 3 6
hn 2014 23 12 11 11
hn 2013 13 13 13 13
yn 2016 15 10 5 7
yn 2015 10 10 10 10
yn 2013 11 11 11 11
*/