函数
create function getstring(@str1 varchar(20),@str2 varchar(20))
returns varchar(40)
as
begin
declare @str varchar(40)
if len(@str1)>=5
begin
set @str=stuff(@str1,2,4,@str2)
set @str=reverse(@str)
set @str=upper(@str)
end
else
set @str='字符串1长度不够'
return @str
end
select dbo.getstring('12345','aaadsfsf')
go
create function getrank(@cname varchar(30))
returns table
as
return
select Sid,score,RANK() over(order by score desc) '名次'
from C,SC
where C.Cid=SC.Cid and cname=@cname
select * from dbo.getrank('Java语言')
create function getc(@cname varchar(30))
returns @tab table(cname varchar(30),优秀 varchar(10),良好 varchar(10),中等 varchar(10),及格 varchar(10),不及格 varchar(10) )
as
begin
declare @num int
select @num= COUNT(*) from C,SC where C.Cid=SC.Cid and cname='C语言'
insert into @tab
select
@cname,
cast(cast(sum(case when score>=90 then 1 else 0 end)*100.0/@num as decimal(5,2)) as varchar)+'%',
cast(cast(sum(case when score>=80 then 1 else 0 end)*100.0/@num as decimal(5,2)) as varchar)+'%',
cast(cast(sum(case when score>=70 then 1 else 0 end)*100.0/@num as decimal(5,2)) as varchar)+'%',
cast(cast(sum(case when score>=60 then 1 else 0 end)*100.0/@num as decimal(5,2)) as varchar)+'%',
cast(cast(sum(case when score<60 then 1 else 0 end)*100.0/@num as decimal(5,2)) as varchar)+'%'
from C,SC
where C.Cid=SC.Cid and cname=@cname
return
end
select * from dbo.getc('C语言')
数据表
create database jxgl
go
use jxgl
go
create table T(
Tid nchar(5) primary key,
name nchar(10) not null,
sex nchar(2) default '男',
workingtime datetime,
plandscape nchar(20),
degree nchar(10),
title nvarchar(6),
department nvarchar(20),
tel nchar(11)
)
create table S(
Sid nchar(10) primary key,
sname nchar(10),
sex nchar(2) default '男',
birthday Date,
age as datediff(yy,birthday,getdate()),
entrydate date,
plandscape nchar(10) default '团员',
nativeplace nvarchar(20),
class as substring(Sid,7,2)
)
create table C(
Cid nchar(3) primary key,
cname nvarchar(20) Not null,
category nchar(4) Not null check(category in('考试','考查')) default '考试',
credit int,
Tid nchar(5) foreign key references T(Tid) on update cascade
)
create table SC(
Sid nchar(10) foreign key references S(Sid) on update cascade on delete cascade,
Cid nchar(3),
score int check(score between 0 and 100),
primary key(Sid,Cid),
foreign key(Cid) references C(Cid) on update cascade on delete cascade
)
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95011', N'赵西明 ', N'男 ', CAST(0x0000768300000000 AS DateTime), N'群众 ', N'硕士 ', N'副教授', N'软件', N'13733152369')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95012', N'李小平 ', N'男 ', CAST(0x0000775E00000000 AS DateTime), N'党员 ', N'硕士 ', N'教授', N'计算机系', N'13733152370')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95013', N'张爽 ', N'男 ', CAST(0x00007BC700000000 AS DateTime), N'群众 ', N'本科 ', N'副教授', N'计算机系', N'13733152371')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95014', N'李丽宁 ', N'女 ', CAST(0x00007BC700000000 AS DateTime), N'党员 ', N'硕士 ', N'副教授', N'计算机系', N'13733152372')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95015', N'张金明 ', N'男 ', CAST(0x0000812C00000000 AS DateTime), N'群众 ', N'硕士 ', N'讲师', N'计算机系', N'13733152373')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95016', N'李燕 ', N'女 ', CAST(0x000082C800000000 AS DateTime), N'党员 ', N'硕士 ', N'讲师', N'计算机系', N'13733152374')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95017', N'宛平 ', N'女 ', CAST(0x0000840B00000000 AS DateTime), N'群众 ', N'博士 ', N'副教授', N'软件', N'13733152375')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95018', N'陈江川 ', N'男 ', CAST(0x0000883900000000 AS DateTime), N'群众 ', N'博士 ', N'讲师', N'软件', N'13733152376')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95019', N'郭新 ', N'男 ', CAST(0x00008CAA00000000 AS DateTime), N'党员 ', N'博士 ', N'讲师', N'软件', N'13733152377')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071101', N'张佳 ', N'女 ', CAST(0x3F1D0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071102', N'好生 ', N'男 ', CAST(0xCD1E0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'广州顺德')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071203', N'徐克 ', N'男 ', CAST(0xBF210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071204', N'叶飞 ', N'女 ', CAST(0xD1210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'党员 ', N'上海')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071205', N'任伟 ', N'男 ', CAST(0x7B220B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'北京顺义')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071206', N'江贺 ', N'男 ', CAST(0x02200B00 AS Date), CAST(0x3D3D0B00 AS Date), N'党员 ', N'福建厦门')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'101', N'计算机基础', N'考试 ', 2, N'95011')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'102', N'C语言', N'考试 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'103', N'计算机组成原理', N'考试 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'104', N'汇编语言', N'考试 ', 3, N'95014')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'105', N'Java语言', N'考查 ', 2, N'95015')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'106', N'操作系统', N'考试 ', 3, N'95016')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'107', N'数据结构', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'108', N'编译原理', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'109', N'网络基础', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'110', N'数据库原理', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'120', N'SQLServer', N'考查 ', 2, N'95018')
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'101', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'102', 70)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'103', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'105', 80)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'102', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'106', 68)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'107', 85)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'109', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'110', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'120', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'102', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'104', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'105', 79)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'107', 55)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'110', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'120', 67)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'101', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'102', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'105', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'106', 95)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'108', 91)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'110', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'101', 63)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'102', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'103', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'107', 58)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'108', 74)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'109', 74)