数据如下:
ID NO_Type sNO eNO
1 1 100 150
2 1 151 200
3 1 201 250
4 1 301 350
5 1 351 400
6 2 51 100
7 2 101 150
8 2 151 200数据关系:NO_type是类别,ID为自动增长型,eNO肯定小于sNO
要求结果:
将前一行eNO和后一行sNO相连的根据NO_Type合并到一起,上面的结果应该是
no_type sno eNO
1 100 250
1 301 400
2 51 200数据量比较大,大概10万条记录
以前实现代码比较慢:
select
a.no_type,a.sNo,min(b.eno) as eno
from
(select * from #tmp c where not exists(select 1 from #tmp where no_type=c.no_type and eno=c.sno-1)) a,
(select * from #tmp d where not exists(select 1 from #tmp where no_type=d.no_type and sno=d.eno+1)) b
where
a.no_type=b.no_type and
and
a.sno<b.eno
group by
a.no_type,a.sno
高手看看是否可以优化或有其它方法?
ID NO_Type sNO eNO
1 1 100 150
2 1 151 200
3 1 201 250
4 1 301 350
5 1 351 400
6 2 51 100
7 2 101 150
8 2 151 200数据关系:NO_type是类别,ID为自动增长型,eNO肯定小于sNO
要求结果:
将前一行eNO和后一行sNO相连的根据NO_Type合并到一起,上面的结果应该是
no_type sno eNO
1 100 250
1 301 400
2 51 200数据量比较大,大概10万条记录
以前实现代码比较慢:
select
a.no_type,a.sNo,min(b.eno) as eno
from
(select * from #tmp c where not exists(select 1 from #tmp where no_type=c.no_type and eno=c.sno-1)) a,
(select * from #tmp d where not exists(select 1 from #tmp where no_type=d.no_type and sno=d.eno+1)) b
where
a.no_type=b.no_type and
and
a.sno<b.eno
group by
a.no_type,a.sno
高手看看是否可以优化或有其它方法?
-------------------------------------------------
这句话怎么解释哦
从结果集里面看不懂
151 200
201 250150+1=151
200+1=201
所以这三行合并成一行sno为100,eno为250
create table #t(id int,type int,sno int,eno int,flag int)
insert into #t(id,type,sno,eno)
select 1 , 1, 100, 150 union all
select 2 , 1, 151, 200 union all
select 3 , 1, 201, 250 union all
select 4 , 1, 301, 350 union all
select 5 , 1, 351, 400 union all
select 6 , 2, 51, 100 union all
select 7 , 2, 101, 150 union all
select 8 , 2, 151, 200declare @type int,@sno int,@eno int,@flag int
set @flag = 0update #t
set @flag = case when type = @type and sno = @eno + 1 then @flag else @flag + 1 end
,@type = type,@eno = eno,flag = @flag
select type,flag,min(sno),max(eno)
from #t
group by type,flagdrop table #ttype flag
----------- ----------- ----------- -----------
1 1 100 250
1 2 301 400
2 3 51 200(所影响的行数为 3 行)
goinsert into #tmp(id,no_type,sno,eno)
select 1 , 1, 100, 150 union all
select 2 , 1, 151, 200 union all
select 3 , 1, 201, 250 union all
select 4 , 1, 301, 350 union all
select 5 , 1, 351, 400 union all
select 6 , 2, 51, 100 union all
select 7 , 2, 101, 150 union all
select 8 , 2, 151, 200
GOselect m.no_type,m.sno,min(n.eno) as eno
from
(select a.* from #tmp a left join #tmp b on a.no_type=b.no_type and a.sno=b.eno+1 WHERE b.id IS NULL) m,
(select a.* from #tmp a left join #tmp b on a.no_type=b.no_type and b.sno=a.eno+1 WHERE b.id IS NULL) n
where
m.no_type=n.no_type and
m.sno<n.eno
group by
m.no_type,m.sno搂主的语句已经很精简了。这个只改了子查询部分,看看能不能快点