求一查询语句

[lzw_0736]

CREATE #temp (cName CHAR(1),re int)
INSERT #temp
SELECT 'A',1 UNION ALL
SELECT 'A',2 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'A',4 UNION ALL
SELECT 'A',5

如何查询得到如下的结果：
cName,re,xh
-------------------
'A',1,1
'A',2,1
'B',3,2
'A',4,3
'A',5,3

也就是说cName字段，如果连续相同，则为同一组序号

[鱼缸里的小泥鳅]

鬼知道你是什么逻辑啊？

[以学习为目的]

主要是判断re是否连续。 给个思路：

declare @num1 int,@num2 int,@i int,@row int
select @row=count(*) from 表名
set @i=1
while(@i<=@row)
	begin
		select @num1=序号 from 表名 where 序号=@i
		select @num2=序号 from 表名 where 序号=@i+1
		if(@num2-@num1<>1)
			begin
				print(cast(@num1 as char)+' 与 '+cast(@num2 as char)+'不连续');
				break;
			end
		set @i=@i+1
	end

[唐诗三百首]

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


select a.cName,a.re,
       (select count(distinct cName)+1 
        from #temp b
        where b.re<
        isnull((select top 1 c.re
                from #temp c
                where c.re<a.re and c.cName<>a.cName
                order by c.re desc),0)+1) 'xh'
 from #temp a
 
/*
cName re          xh
----- ----------- -----------
A     1           1
A     2           1
B     3           2
A     4           3
A     5           3

(5 row(s) affected)
*/

[lzw_0736]

XH（序号）是需要通过查询新产生的字段.

[發糞塗牆]

CREATE table #temp (cName CHAR(1),re int)
INSERT #temp
SELECT 'A',1 UNION ALL
SELECT 'A',2 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'A',4 UNION ALL
SELECT 'A',5
go

;with t as
(
select 
	*,
	re-ROW_NUMBER()over(partition by cName order by re asc) as t from #temp
),
m as
(
select
	cName,t,MIN(re) as minre,MAX(re) as maxre from t group by cName,t
),
o as
(
select row=ROW_NUMBER()over(order by maxre asc),* from m
)
select
	a.*,o.row from #temp a
inner join o on a.cName=o.cName and a.re between o.minre and o.maxre
/*
cName	re	row
--------------------------------
A	1	1
A	2	1
B	3	2
A	4	3
A	5	3
*/

[唐诗三百首]

sorry,3楼代码有Bug,修正一下,

create table #temp (cName CHAR(1),re int)

-- 测试1
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


with t as
(select a.cName,a.re,
        isnull((select top 1 c.re
                from #temp c
                where c.re<a.re and c.cName<>a.cName
                order by c.re desc),0)+1 'fr'
 from #temp a)
select a.cName,a.re,
      (select count(distinct b.cName+rtrim(b.fr))+1 
       from t b
       where b.re<a.fr) 'xh'
from t a

/*
cName re          xh
----- ----------- -----------
A     1           1
A     2           1
B     3           2
A     4           3
A     5           3

(5 row(s) affected)
*/


-- 测试2
truncate table #temp

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 a.cName,a.re,
        isnull((select top 1 c.re
                from #temp c
                where c.re<a.re and c.cName<>a.cName
                order by c.re desc),0)+1 'fr'
 from #temp a)
select a.cName,a.re,
      (select count(distinct b.cName+rtrim(b.fr))+1 
       from t b
       where b.re<a.fr) 'xh'
from t a

/*
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

(12 row(s) affected)
*/

[eOuch]

楼上的人们牛啊

[LongRui888]

是这样吗：

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
*/

[LongRui888]

对上面的代码，做了改进：

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
*/

[lzw_0736]

謝謝各位大牛！

[小魚人]

CREATE table #temp (cName CHAR(1),re int)
 INSERT #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 
select * from #temp
;with cte as 
(select * ,rn=1 from #temp where re=1
union all
select   b.cName ,b.re ,case when a.cName =b.cName then rn  else rn+1 end as xh from cte a join #temp b
 on b.re =a.re+1
)

select * from cte
 
 drop table #temp