提取游标给表变量

linyu27

定义了一游标c_tmp
open c_tmp
fetch next from c_tmp
  insert into tablename select * from c_tmp（这时插入的就是游标所处的位置所提出的记录）是吗？

zhuzhichao

open c_tmp
fetch next from c_tmp into @values1,@values2......
insert into tablename values(@values1,@values2......


不要老用Oracle的思想來學習SQL Server!!

alex_cs

初始化环境：
--创建表
create table test (
billno varchar(10) NOT NULL ,
billamt decimal(18,4) NOT NULL
)
--插入初始纪录
insert test values ('itpub00005', 500.0000)
insert test values ('itpub00001',100.0000)
insert test values ('itpub00002',200.0000)
insert test values ('itpub00003',300.0000)
insert test values ('itpub00006',600.0000)
insert test values ('itpub00008',800.0000)
insert test values ('itpub00009',900.0000)
insert test values ('itpub00010',1000.0000)
insert test values ('itpub00012',1200.0000)
insert test values ('itpub00015',1500.0000)
insert test values ('itpub00016',1600.0000)
insert test values ('itpub00018',1800.0000)
insert test values ('itpub00019',1900.0000)


一步到位做查询：
select rtrim(min(begin_range)) +' ~ '+ end_range as billno,
( select sum(billamt)
from test
where billno between min(begin_range) and end_range) as billamt
from
(select billno as begin_range,
( select min(billno)
from test as f3
where f3.billno > (select min(billno) from test)
and not exists (select *
from test as f4
where (left(f4.billno, len(f4.billno)-5) +
right( rtrim('00000'+
convert( char(5),
convert(integer,
right(rtrim(f4.billno),5))-1)),5)
) = f3.billno) and
f3.billno >= f1.billno
) as end_range
from test as f1
) as f5
group by end_range



结果：
billno                  billamt                                 
----------------------- ----------------------------
itpub00001 ~ itpub00003 600.0000
itpub00005 ~ itpub00006 1100.0000
itpub00008 ~ itpub00010 2700.0000
itpub00012 ~ itpub00012 1200.0000
itpub00015 ~ itpub00016 3100.0000
itpub00018 ~ itpub00019 3700.0000