1、求一查询语句 CREATE #temp (cName CHAR(1),re int)INSERT #tempSELECT '
A'
,1 UNION ALLSELECT '
A'
,2 UNION ALLSELECT '
B'
,3 UNION ALLSELECT '
A'
,4 UNION ALLSELECT '
A'
,5如何查询得到如下的结果:cName,re,xh-------------------'
A'
,1,1'
A'
,2,1'
B'
,3,2'
A'
,4,3'
A'
,5,3下面是我的解答:[sql] select *, dense_rank() over(order by case when exists(select t2.re from #temp t2 where t1.cname = t2.cname and t1.re= t2.re + 1) then (select t2.re from #temp t2 where t1.cname = t2.cname and t1.re= t2.re + 1) else t1.re end ) as xh from #temp t1 /* cName re xh A 1 1 A 2 1 B 3 2 A 4 3 A 5 3 */ 但是这个解答是有问题的,因为当连续的记录超过3条时,就会有问题,所以修改了一下,这个是正确的解法:[sql] create table #temp (cName CHAR(1),re int) insert into #temp SELECT '
A'
,1 UNION ALL SELECT '
A'
,2 UNION ALL SELECT '
B'
,3 UNION ALL SELECT '
A'
,4 UNION ALL SELECT '
A'
,5 union all SELECT '
A'
,6 union all SELECT '
A'
,7 union all SELECT '
D'
,8 union all SELECT '
D'
,9 union all SELECT '
D'
,10 union all select '
B'
,11 union all select '
A'
,12 ;
with t as ( select *, row_number() over(partition by cname order by re) as rownum from #temp ) select cname, re, dense_rank() over(order by case when exists(select min(t2.re) from t t2 where t1.cname = t2.cname and t1.re-t1.rownum= t2.re-t2.rownum) then (select min(t2.re) from t t2 where t1.cname = t2.cname and t1.re-t1.rownum= t2.re-t2.rownum) else t1.re end ) as xh from t t1 /*cname re xh A 1 1 A 2 1 B 3 2 A 4 3 A 5 3 A 6 3 A 7 3 D 8 4 D 9 4 D 10 4 B 11 5 A 12 6 */