-
本版专家分：7结帖率 97.48% -
数据
A B C D E F G
CF004 DZ H 005 016 12 100
CF004 DZ H 002 004 3 200
CF004 DZ F 006 016 11 300
CF004 DZ F 002 004 3 400
CF005 DZ D 005 017 13 100
CF005 DZ D 002 004 3 400
结果
A B C D E F G
CF004 DZ H 002 016 15 300
CF004 DZ F 006 016 11 300
CF004 DZ F 002 004 3 400
CF005 DZ D 002 017 16 500
请教各位大神，怎么按照如上的数据，根据E列的最大值+1等于D列的最小值这个条件，进行排序，把数据统计给结果这样？
怎么按![]()
-
本版专家分：15714
-
-
蓝花
2016年9月 MS-SQL Server大版内专家分月排行榜第三
2016年3月 MS-SQL Server大版内专家分月排行榜第三
-
-
没看明白你的意思，根据你的结果退出来的写法。
E的最小值加上1等D的最大值则合并，select出来的却是组中的E的最大值和D的最小值
; with TestTable(A,B,C,D,E,F,G) AS(
select 'CF004','DZ','H','005','016',12,100 union all
select 'CF004','DZ','H','002','004',3,200 union all
select 'CF004','DZ','F','006','016',11,300 union all
select 'CF004','DZ','F','002','004',3,400 union all
select 'CF005','DZ','D','005','017',13,100 union all
select 'CF005','DZ','D','002','004',3,400
)
select A,B,C,min(D) as D,max(E) as E,sum(F) as F,sum(G) as G from (
select *,row_number()over(partition by A,B,C order by E DESC) as rn_E,row_number()over(partition by A,B,C order by D) as rn_D
,min(E)over(partition by A,B,C) as min_E,max(D)over(partition by A,B,C) as max_D
from TestTable
) as t
group by A,B,C,case when min_E+1-max_D=0 then '0' else convert(varchar(100),newid()) end
A B C D E F G
1 CF004 DZ F 006 016 11 300
2 CF004 DZ F 002 004 3 400
3 CF004 DZ H 002 016 15 300
4 CF005 DZ D 002 017 16 500
-
本版专家分：7
-
写错了，是根据A,B,C分组，H,G求和，E,F显示最小值和最大值
-
本版专家分：171 -
select A,B,C,MIN(E)E,MAX(F)F,SUM(G)G from TestTable
group by A,B,C
H在哪
-
本版专家分：7
-
我写错了，A,B,C分组,D,E判断E的最大值是否与D的最小值相连，即4,5这种（1楼数据的第一条和第二条数据，第5条和第6条数据），如果相连，那么查询结果是D的最小值和E的最大值。
F,G求和，如果不相连，即4,6这种（1楼数据的第三条和第四条数据），查询结果不能合并这两条数据![]()
-
本版专家分：7709
-
-
蓝花
2016年2月 MS-SQL Server大版内专家分月排行榜第三
2016年1月 MS-SQL Server大版内专家分月排行榜第三
-
-
; with TestTable(A,B,C,D,E,F,G) AS(
select 'CF004','DZ','H','005','016',12,100 union ALL
select 'CF004','DZ','H','002','004',3,200 union all
select 'CF004','DZ','F','006','016',11,300 union all
select 'CF004','DZ','F','002','004',3,400 union all
select 'CF005','DZ','D','005','017',13,100 union all
select 'CF005','DZ','D','002','004',3,400
)
SELECT a.A,a.B,a.C,a.D,b.E ,SUM(c.f),SUM(c.G)
FROM (
SELECT A,B,C,D,
ROW_NUMBER() OVER (PARTITION BY A,B,C ORDER BY D) AS RN
FROM TestTable a
WHERE NOT EXISTS(SELECT * FROM TestTable WHERE a.A = A AND a.B = B AND a.C = C AND a.D = E + 1)) a
INNER JOIN (
SELECT A,B,C,E,
ROW_NUMBER() OVER (PARTITION BY A,B,C ORDER BY F) AS RN
FROM TestTable a
WHERE NOT EXISTS(SELECT * FROM TestTable WHERE a.A = A AND a.B = B AND a.C = C AND D = a.E + 1)) b ON a.RN = b.RN AND b.A = a.A AND b.B = a.B AND b.C = a.C
INNER JOIN TestTable c ON c.A = a.A AND c.B = a.B AND c.C = a.C AND c.D >= a.D AND c.E <= b.E
GROUP BY a.A,a.B,a.C,a.D,b.E
