下面是个简单的代码示例(可是有错误啊),请大家帮我挑挑毛病
示例没有进行中间的计算,只是做了一个类似笛卡儿的乘积,
将所有的数据都插入了临时表里,以示说明CREATE PROCEDURE Pzlx1
AS
begin
declare @studentNo char(15),@courseNo char(10)
set @studentNo=''
set @courseNo=''declare studentCursor CURSOR for
select student.studentNo
from studentdeclare courseCursor CURSOR for
select courseNo
from course if exists (select studentNo from ##PaiMingTable)
drop table ##TableCREATE TABLE ##Table
(studentNo char(15) PRIMARY KEY,courseNo char(10))open studentCursor
fetch next from studentCursor
into @studentNowhile @@FETCH_STATUS=0
begin
close studentCursor
open courseCursor
fetch next from courseCursor
into @courseNo
while @@FETCH_STATUS=0
begin
insert ##PaiMingTable
values ( @studentNo, @courseNo)
fetch next from courseCursor
into @courseNo
end --课程游标循环结束
close courseCursor
open studentCursor
fetch next from studentCursor
into @studentNo
end
close studentCursor
deallocate studentCursor
deallocate courseCursorselect *
from ##Table
end
GO
示例没有进行中间的计算,只是做了一个类似笛卡儿的乘积,
将所有的数据都插入了临时表里,以示说明CREATE PROCEDURE Pzlx1
AS
begin
declare @studentNo char(15),@courseNo char(10)
set @studentNo=''
set @courseNo=''declare studentCursor CURSOR for
select student.studentNo
from studentdeclare courseCursor CURSOR for
select courseNo
from course if exists (select studentNo from ##PaiMingTable)
drop table ##TableCREATE TABLE ##Table
(studentNo char(15) PRIMARY KEY,courseNo char(10))open studentCursor
fetch next from studentCursor
into @studentNowhile @@FETCH_STATUS=0
begin
close studentCursor
open courseCursor
fetch next from courseCursor
into @courseNo
while @@FETCH_STATUS=0
begin
insert ##PaiMingTable
values ( @studentNo, @courseNo)
fetch next from courseCursor
into @courseNo
end --课程游标循环结束
close courseCursor
open studentCursor
fetch next from studentCursor
into @studentNo
end
close studentCursor
deallocate studentCursor
deallocate courseCursorselect *
from ##Table
end
GO
as
begin
create table #aa(a int)
declare cursor_1 cursor for select a from a
open cursor_1
fetch cursor_1 into @i
while @@fetch_status=0
begin
declare cursor_2 cursor for select count(*) from b where a=@i
open cursor_2
fetch cursor_2 into @j
while @@fetch_status=0
begin
insert #aa values(@j)
fetch cursor_2 into @j
end
fetch cursor_1 into @i
end
select * from #aa
end不过好象一句话可以高定:select 编号,count(*) 统计 into #临时表 from 表b where 编号 in (select 编号 from 表a)select * from #临时表