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
*/
#2
你提供的汇总结果没有问题吧?
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
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
*/
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
*/
你看看是不是你想要的,2015年hn数据实际算出来和你贴出的有点出入
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
*/
谢谢啊!
#9
另外一个问题也找到,取平均时因为元素是整数,所以AVG返回的也是整数,改浮点就行了.
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
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
*/
你看看是不是你想要的,2015年hn数据实际算出来和你贴出的有点出入
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
*/
谢谢啊!
#1
你看看是不是你想要的,2015年hn数据实际算出来和你贴出的有点出入
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
*/
#2
你提供的汇总结果没有问题吧?
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
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
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
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
*/
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
*/
你看看是不是你想要的,2015年hn数据实际算出来和你贴出的有点出入
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
*/
谢谢啊!
#9
另外一个问题也找到,取平均时因为元素是整数,所以AVG返回的也是整数,改浮点就行了.
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
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
*/
你看看是不是你想要的,2015年hn数据实际算出来和你贴出的有点出入
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
*/