这样的表用SQL的游标怎么实现？？？(80分)

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-18

• #1

表如下
invoiceno money
1 2000
1 3000
2 600
2 400
1 4000
统计结果为
invoiceno paymoney total
1 2000+3000+4000 9000
2 600+400 1000
也就是根据invoiceno分组,列出每次的付款情况（字符串）和总的付款情况？

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-18

• #2

第一个是原表，第二个即统计结果是我要的表，主要难在paymoney，它是个过程，total是结果 []

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-18

• #3

我先创建的临时表,在用的游标,临时表跟你的查询结果结构一样
create table #temp (filed1 int,field2 varchar(200),field3 int)
declare sum_cursor cursor for
select invoiceno,sum(money) from 表名 group by invoiceno
open sum_cursor
declare @id int,@sum int,@tem varchar(200)
set @id=0
set @sum=0
set @tem=''
fetch next from sum_cursor into @id,@sum
while @@fetch_status=0
begin
select @tem=@tem+convert(varchar,money)+'+' from 表名where vipkh=@id
insert into #temp values(@id,substring(@tem,1,len(@tem)-1),@sum)
fetch next from sum_cursor into @id,@sum
end
close sum_cursor
deallocate sum_cursor
select * from #temp
drop table #temp
我没在里面加上0值＆null的判断,这个你要注意一下,我用别的表试过,可以.不过这个东西感觉用游标还是不太好.

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-18

• #4

其实我没一定要用游标，但只用SQL 语句觉得paymoney这个字段不好实现

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-18

• #5

select @tem=@tem+convert(varchar,money)+'+' from 表名where vipkh=@id

里面的vipkh是什么？

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-18

• #6

哦,呵呵,那个我是测试表的字段,你把它改成你的表字段invoiceno就行了

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-19

• #7

我想可以不用游标实现,通过内置表函数不错
id 对应你的invoiceno字段
amount对应你的money字段
test对应你的表
create function sum_func (@id int)
returns varchar(200)
begin
declare @str varchar(200)
set @str=''
select @str=
case
when @str='' then convert(varchar,amount)
else @str+'+'+convert(varchar,amount)
end
from test where id=@id
return @str
end
最后用
select id,sum(amount),dbo.sum_func (id) from test group by id
就可以得到你要的结果

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-20

• #8

to hityou:我用你的存储过程得出的结果是
1 2000+3000+4000 9000
2 2000+3000+4000+600+400 1000

这个那里出错了？？

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #9

不会把,我在查询分析器里面没问题啊

 

O

onlyonekgx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #10

写个函数不就OK了

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-24

• #11

to hityou:我用你的存储过程得出的结果是
1 2000+3000+4000 9000
2 2000+3000+4000+600+400 1000

paymoney的值不停的在累加

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-24

• #12

我这里没有任何问题,
试验数据
1 100 0 2006-05-18 11:13:07.930 NULL
1 100 0 2006-05-18 11:13:07.930 NULL
1 100 50 2006-05-18 11:13:07.930 NULL
2 300 50 2006-05-18 11:13:07.930 qqnihao
2 700 100 2006-05-18 11:13:07.930 qqnihao
2 200 150 2006-05-18 11:13:07.930 qqnihao
1 100 100 2006-05-18 11:13:07.930 NULL
3 200 NULL 2006-05-24 08:39:31.043 NULL
3 300 NULL 2006-05-24 08:39:31.043 NULL
3 400 NULL 2006-05-24 08:39:31.043 NULL

我统计前两项试验结果
1 400 100+100+100+100
2 1200 300+700+200
3 900 200+300+400
用的就是上面那个,你看看,是不是别的地方错了,我用的是
select id,sum(amount),dbo.sum_func (id) from test group by id,不是用游标的那个

 

B

bgqy2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-24

• #13

我认为问题出在select @tem=@tem+convert(varchar,money)+'+' from 表名where vipkh=@id
中@tem值在开始一个新的@id时没有置0引起的。

 

B

bgqy2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-24

• #14

改了下，应该没问题了。
CREATE TABLE #temp(filed1 int, field2 varchar(200), field3 int)
DECLARE sum_cursor CURSOR FOR SELECT year, SUM(product)
FROM table1
GROUP BY year
OPEN sum_cursor DECLARE @id int, @sum int, @tem varchar(200),@temid int
SET @id = 0
SET @sum = 0
SET @tem = ''
SET @temid = 0
FETCH next
FROM sum_cursor
INTO @id, @sum WHILE (@@fetch_status = 0 )BEGIN
if(@temid!=@id)SET @tem = ''
SET @temid = @id
SELECT @tem = @tem + CONVERT(varchar, product) + '+'
FROM table1 WHERE year = @id
INSERT INTO #temp
VALUES (@id, substring(@tem, 1, len(@tem) - 1), @sum)
FETCH next
FROM sum_cursor
INTO @id, @sum END CLOSE sum_cursor DEALLOCATE sum_cursor
SELECT * FROM #temp
DROP TABLE #temp

你把里面相应的字段改成你表里就ok了。

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-06-08

• #15

--数据准备
create table tmp (invoiceno int, money decimal(28,8))
insert tmp values ('1','2000')
insert tmp values ('1','3000')
insert tmp values ('2','600')
insert tmp values ('2','400')
insert tmp values ('1','4000')

select *
from tmp

--存储过程


create proc sp_temp
as
declare @temp table(
invoiceno int,
paymoney varchar(100),
[money] decimal(28,8))
declare @invoiceno int,
@money decimal(28,8)

declare CurTree CurSor for select invoiceno, [money] from tmp order by invoiceno
open CurTree
fetch from CurTree into @invoiceno, @money

while @@fetch_status = 0
begin
if (select count(*) from @temp where invoiceno = @invoiceno)>0
begin
update @temp set [money] = @money + [money], paymoney = paymoney +'+'+convert(varchar,@money)
where invoiceno = @invoiceno

end
else
insert @temp
values (@invoiceno,convert(varchar,@money), @money)
fetch next from CurTree into @invoiceno, @money
end

close CurTree
deallocate CurTree

select *
from @temp

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-06-08

• #16

这样写应该更简单易懂些吧

 

X

xl4772

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-06-08

• #17

多人接受答案了。