如何自定义函数生成表? 场景
学生管理系统有一些实际需求:
1、 创建成绩表t_score,表的结构和内容如下:
S_number(char(8)) C_number(char(4)) Score(int)
20030101 001 60
20030102 001 65
20030103 001 55
20030101 002 80
20030102 002 85
20030103 002 90
20030101 003 70
20030104 003 80
20030201 005 68任务:
1、 创建成绩表
2、 编写函数,统计指定班级的成绩并生成课程成绩统计表,该表应包含两列:c_number(char)),和aver(decimal(6,2))
学生管理系统有一些实际需求:
1、 创建成绩表t_score,表的结构和内容如下:
S_number(char(8)) C_number(char(4)) Score(int)
20030101 001 60
20030102 001 65
20030103 001 55
20030101 002 80
20030102 002 85
20030103 002 90
20030101 003 70
20030104 003 80
20030201 005 68任务:
1、 创建成绩表
2、 编写函数,统计指定班级的成绩并生成课程成绩统计表,该表应包含两列:c_number(char)),和aver(decimal(6,2))
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@S_number int)
returns nvarchar(100)
as
begin
select c_number,avg(Score) as [aver] from t_score where S_number=@S_number
end
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@S_number int)
returns nvarchar(100)
as
begin
declare @avgscore dec(6,2)
select c_number,@avgscore =avg(Score) as [aver] from t_score where S_number=@S_number
end
go
go
create function f_s(@C_number varchar(8))
return table
as
return (select c_number,avg(Score) from t_score where C_number = @C_number group by c_number)
go
go
create function f_s(@C_number varchar(8))
returns @r table(C_number varchar(4), Score int)
as
begin
insert @r
select c_number,avg(Score) from t_score where C_number = @C_number group by c_number
return
end
go可以这样,或把3L改动如下也可以:create table t_score(S_number char(8) primary key ,C_number char(4), Score int)
go
create function f_s1(@C_number varchar(8))
returns table
as
return (select c_number,avg(Score) Score from t_score where C_number = @C_number group by c_number)
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@S_number int)
returns nvarchar(100)
as
begin
declare @avgscore dec(6,2)
select c_number,@avgscore =avg(Score) as [aver] from t_score group by c_number where S_number=@S_number
end
go
SELECT * FROM [数据库名].[架构名].[函数名] ('参数')
表值函数如果在当前数据库操作,可以这样:SELECT * FROM [函数名] ('参数')也可以像你那样。
[/Quote]谢谢~