求一存储过程,谢~~

[PLMM007]

一张表a

在a里有字段id

id name
1 test1
2 test2
3 test3

我要根据id的值一个一个对统计数据
统计时我的一个变量值就是id

如select count(*) from b where id=@a.id

要用while循环来将结果写到一临时表中

[yoki]

exec ('select id,count(*) from b where b.id in (select id from a where id ='@id +'))'

[txlicenhe]

1:如下就可以了，没必要用循环吧，效率差。
Select id,count(*) as cnt into #tmp
group by id

[txlicenhe]

1:如下就可以了，没必要用循环吧，效率差。
Select id,count(*) as cnt into #tmp from 表a
group by id

[pengdali]

select id,count(*) num into #临时表 from b where id in (select id from a) group by id

select * from #临时表

go
drop table #临时表

[pengdali]

select id,count(*) num into #临时表 from b where id in (select id from a) group by id

select * from #临时表

go
drop table #临时表

[PLMM007]

我统计的不是一个值
我把统计的结果放到一个个变量里了


如select @a=count(*) from t where id=@t

@t就是a表的一个id值，

[yoki]

create Proc P_GetCount
@id int,
@R_COUNTS int output
as
begin
declare @sql varchar(8000)
select @sql='select'+@R_COUNTS+'=count(*) from b where b.id in (select id from a where id ='+@id +'))'
exec (@sql)
end

[PLMM007]

而且每统计一个结果到变量的条件都不同
一种情况：
select @a=count(*) from t where id=@t

另一种
select @b=datediff(day,a,b) from c where c.id=@t

多了，然后我想把
@a,@b 等作为字段写到一个一个临时表中

[PLMM007]

因为@t有多个值，如上例分别有1,2,3，则我最终生成的临时表也有三条记录
它们含的字段内容，分里有@a,@b……组成

[sdhdy]

一种情况：
select @a=count(*) from t where id=@t

另一种
select @b=datediff(day,a,b) from c where c.id=@t
这两中情况，怎么判断呀！

[PLMM007]

我是想这样


select id from a -->放到一个记录集里

用while 取得id

while ……
select @a=count(*) from t where id=dr("id")
select @b=datediff(day,a,b) from c where c.id=dr("id")


select @a,@b…… into #temp

[PLMM007]

up

[sdhdy]

create table #temp(F1 int ,F2 int)
declare @id int
declare @a int
declare @b int
set @a=0
set @b=0
DECLARE cur_test CURSOR FOR select id from a
OPEN cur_test
FETCH NEXT FROM cur_test into @id
while @@fetch_status=0
begin
select @a=count(*) from t where id=@id
select @b=datediff(day,a,b) from c where c.id=@id
insert #temp values(@a,@b)
FETCH NEXT FROM cur_test into @id
end
close cur_test
deallocate cur_test

[sdhdy]

create proc test
as
declare @id int
declare @a int
declare @b int
set @a=0
set @b=0
create table #temp(F1 int ,F2 int)
DECLARE cur_test CURSOR FOR select id from a
OPEN cur_test
FETCH NEXT FROM cur_test into @id
while @@fetch_status=0
begin
select @a=count(*) from t where id=@id
select @b=datediff(day,a,b) from c where c.id=@id
insert #temp values(@a,@b)
FETCH NEXT FROM cur_test into @id
end
select * from #temp
drop table #temp
close cur_test
deallocate cur_test

[PLMM007]

又一问

select a,b,c,exec sp_name a.id from a where t>0

exec sp_name a.id 会返回几个值，如'123','456'

应该怎么样写？

[pengdali]

select a.num1,b.num2 into #temp from (
select id,count(*) num1 from t where id in (select id from a) group by id) a
,(
select id,datediff(day,a,b) num2 from c where id in (select id from a)
) b where a.id=b.id


select * from #temp

go
drop table #temp