2个字段首位相连的问题

[7年]

数据
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列的最小值这个条件，进行排序，把数据统计给结果这样？
怎么按

[道素]

没看明白你的意思，根据你的结果退出来的写法。 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年]

求E的最小值与D的最大值相连，根据A,B分组

[7年]

引用 2 楼 zzflover 的回复:

求E的最小值与D的最大值相连，根据A,B分组

写错了，是根据A,B,C分组，H,G求和，E,F显示最小值和最大值

[kevin87923]

select A,B,C,MIN(E)E,MAX(F)F,SUM(G)G from TestTable 
group by A,B,C

H在哪

[7年]

引用 4 楼 kevin87923 的回复:

select A,B,C,MIN(E)E,MAX(F)F,SUM(G)G from TestTable 
group by A,B,C

H在哪

我写错了，A,B,C分组,D,E判断E的最大值是否与D的最小值相连，即4,5这种（1楼数据的第一条和第二条数据，第5条和第6条数据），如果相连，那么查询结果是D的最小值和E的最大值。 F,G求和，如果不相连，即4,6这种（1楼数据的第三条和第四条数据），查询结果不能合并这两条数据

[Ginnnnnnnn]

; 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