select count(col2) where col1 between 1 to 2 没有调过,不知道对不对
create table tb_1(col1 int,col2 int) --drop table tb_1 insert tb_1 select 1, 100 insert tb_1 select 2, 100 insert tb_1 select 4, 100 insert tb_1 select 6, 100 insert tb_1 select 7, 100 insert tb_1 select 8, 100 ------------------用临时表中转一下,2005估计一句话也能写下来用row什么函数 select col1,identity(int,1,1) id into #1 from tb_1 a where not exists(select 0 from tb_1 b where a.col1=b.col1+1) order by col1 select col1,identity(int,1,1) id into #2 from tb_1 a where not exists(select 0 from tb_1 b where a.col1=b.col1-1) order by col1 ------------------------------ select rtrim(A.a)+'-'+rtrim(A.b)xxx,sum(tb_1.col2) from tb_1 left join(select #1.col1 a,#2.col1 b from #1,#2 where #1.id=#2.id)A on tb_1.col1 between A.a and A.b group by A.a,A.b ------------------- 1-2 200 4-4 100 6-8 300
没有调过,不知道对不对
insert tb_1 select 1, 100
insert tb_1 select 2, 100
insert tb_1 select 4, 100
insert tb_1 select 6, 100
insert tb_1 select 7, 100
insert tb_1 select 8, 100
------------------用临时表中转一下,2005估计一句话也能写下来用row什么函数
select col1,identity(int,1,1) id into #1 from tb_1 a where not exists(select 0 from tb_1 b where a.col1=b.col1+1) order by col1
select col1,identity(int,1,1) id into #2 from tb_1 a where not exists(select 0 from tb_1 b where a.col1=b.col1-1) order by col1
------------------------------
select rtrim(A.a)+'-'+rtrim(A.b)xxx,sum(tb_1.col2) from tb_1 left join(select #1.col1 a,#2.col1 b from #1,#2 where #1.id=#2.id)A
on tb_1.col1 between A.a and A.b group by A.a,A.b
-------------------
1-2 200
4-4 100
6-8 300