另外一张表B 字段有 id,idA,tm
我要得到的数据是根据sum=s1+s2在历年来的最大值(sunMax)、最小值(sunMin)、平均值(sumAvg)及ssAT=当年(s1+s2)-sumAvg。比如：2016年那么就要求2016年以前的数据的最大值、最小值、平均值，ssAT=2016的数据-以前年度的平均值
如：A表 B表：
idA xzq s1 s2 id idA tm
1 hn 4 6 4 1 2016-02-01
2 hn 6 7 5 2 2016-03-01
3 hn 3 4 6 3 2016-05-01
4 hn 2 1 7 4 2015-02-01
5 hn 8 1 8 5 2015-03-01
6 hn 7 5 9 6 2014-05-01
7 hn 5 8 10 7 2013-02-01
8 hn 3 8 11 8 2014-06-01
9 hn 6 2 12 9 2015-07-01
10 yn 3 2 13 10 2016-03-01
11 yn 7 3 14 11 2015-05-01
12 yn 6 5 15 12 2013-04-01
13 yn 8 2 16 13 2016-08-01
得到的值为：
xzq tm sum sumMax sumMin sumAvg ssAT
hn 2016 30 23 13 18.67 11.33
hn 2015 20 23 13 18 2
hn 2014 23 13 13 13 10
hn 2013 13 0 0 0 0
yn 2016 15 11 10 10.5 4.5
yn 2015 10 11 11 11 -1
yn 2013 11 0 0 0 0
9 个解决方案
#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
/*
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
*/
#3
我提供的结果是对啊！
#4
你好！我是林元校，有一年的编码经验。贵公司的HR觉得我的简历符合贵公司招聘的程序员一职，让我过来面试。
“结果”表中第一四列，第一行的23是怎么得到的？
是不是这样：
1，将第行的s1+s2
select A.idA
,A.xzq
,A.s1
,A.s2
,s1Adds2=A.s1+A.s2
,B.id
,B.idA
,B.tm
from A
inner join B
on A.idA=B.idA
order by A.xzq,B.tm desc
2、从计算结果中找出23
#5
直接忽略那段话，你明白的。
#6
hn 2016 30 23 13 18.67 11.33
hn 2015 20 23 13 18 2
hn 2014 23 13 13 13 10
hn 2013 13 0 0 0 0
yn 2016 15 11 10 10.5 4.5
yn 2015 10 11 11 11 -1
yn 2013 11 0 0 0 0
按xzq 分组tm是2016,最大值23就算2015、2014、2013的sum=（s1+s2）分别是20,23,13这三个数当中的最大值就是23
按xzq 分组 tm是2015,最大值23就算2014、2013的sum=（s1+s2）分别是23,13 这两个数当中的最大值也是23
最小值和平均值也是这样算
hn 2015 20 23 13 18 2
hn 2014 23 13 13 13 10
hn 2013 13 0 0 0 0
yn 2016 15 11 10 10.5 4.5
yn 2015 10 11 11 11 -1
yn 2013 11 0 0 0 0
按xzq 分组tm是2016,最大值23就算2015、2014、2013的sum=（s1+s2）分别是20,23,13这三个数当中的最大值就是23
按xzq 分组 tm是2015,最大值23就算2014、2013的sum=（s1+s2）分别是23,13 这两个数当中的最大值也是23
最小值和平均值也是这样算
#7
谢谢啊！
#8
我造的数据错误了，增加了不少2015的记录.另外你的sumAVG事什么算法，你得出的18.67是怎么算出的?
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
*/
#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
/*
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
*/
#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
/*
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
*/
#3
我提供的结果是对啊！
#4
你好！我是林元校，有一年的编码经验。贵公司的HR觉得我的简历符合贵公司招聘的程序员一职，让我过来面试。
“结果”表中第一四列，第一行的23是怎么得到的？
是不是这样：
1，将第行的s1+s2
select A.idA
,A.xzq
,A.s1
,A.s2
,s1Adds2=A.s1+A.s2
,B.id
,B.idA
,B.tm
from A
inner join B
on A.idA=B.idA
order by A.xzq,B.tm desc
2、从计算结果中找出23
#5
直接忽略那段话，你明白的。
#6
hn 2016 30 23 13 18.67 11.33
hn 2015 20 23 13 18 2
hn 2014 23 13 13 13 10
hn 2013 13 0 0 0 0
yn 2016 15 11 10 10.5 4.5
yn 2015 10 11 11 11 -1
yn 2013 11 0 0 0 0
按xzq 分组tm是2016,最大值23就算2015、2014、2013的sum=（s1+s2）分别是20,23,13这三个数当中的最大值就是23
按xzq 分组 tm是2015,最大值23就算2014、2013的sum=（s1+s2）分别是23,13 这两个数当中的最大值也是23
最小值和平均值也是这样算
hn 2015 20 23 13 18 2
hn 2014 23 13 13 13 10
hn 2013 13 0 0 0 0
yn 2016 15 11 10 10.5 4.5
yn 2015 10 11 11 11 -1
yn 2013 11 0 0 0 0
按xzq 分组tm是2016,最大值23就算2015、2014、2013的sum=（s1+s2）分别是20,23,13这三个数当中的最大值就是23
按xzq 分组 tm是2015,最大值23就算2014、2013的sum=（s1+s2）分别是23,13 这两个数当中的最大值也是23
最小值和平均值也是这样算
#7
谢谢啊！
#8
我造的数据错误了，增加了不少2015的记录.另外你的sumAVG事什么算法，你得出的18.67是怎么算出的?
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
*/
#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
/*
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
*/