-- step 1
create Table #t (studentID char(10), [name] varchar(8), startDate char(10) Check(
substring(startDate,5,1)='-' AND substring(startDate,8,1)='-' AND
isdate(startDate)=1
))
INSERT #t values('1','a','012956')
INSERT #t values('112996')
insert #t values('1','a','2001-01-04')
SELECT * FROM #t
drop table #t
-- step 2
create table #tc ([name] varchar(8), chinese float, math float)
insert #tc
select 'a', 80, 90
union all select 'b', 85, 88
union all select 'c', 77, 93
union all select 'd', 80, 90
union all select 'e', 99, 100
create procedure showRank
as
begin
select [name], chinese, math, chinese+math as total,
rank() over (order by chinese+math, chinese) as rank
from #tc
order by chinese+math
end
exec showRank
drop table #tc
-- step 1
create Table #t (studentID char(10), [name] varchar(8), startDate char(10) Check(
substring(startDate,5,1)='-' AND substring(startDate,8,1)='-' AND
isdate(startDate)=1
))
INSERT #t values('1','a','012956')
INSERT #t values('1', 'a', '1129-9-6')
insert #t values('1','a','2001-01-04')
SELECT * FROM #t
drop table #t
-- step 2
create table #tc ([name] varchar(8), chinese float, math float)
insert #tc
select 'a', 80, 90
union all select 'b', 85, 88
union all select 'c', 77, 93
union all select 'd', 80, 90
union all select 'e', 99, 100
union all select 'f', 90, 80
alter procedure showRank
as
begin
select [name], chinese, math, chinese+math as total,
rank() over (order by chinese+math desc, chinese desc) as rank
from #tc
order by rank
end
exec showRank
drop table #tc
--对输入的数据进行约束
create table t(studentID char(10), [name] varchar(8),
startDate char(10) Check (isdate(startdate)=1 and cast(startdate as datetime)=convert(datetime,startdate,120)))
insert into t
select 'aa','bb','77799820'
union all select 'bb','ggg','2007-10-11'
union all select 'cc','ddd','2007-20-11'
select * from t
--创建测试表
create table tbl ([name] varchar(8), chinese float, math float,zong float,px int)
insert tbl
select 'a', 80, 90,null,null
union all select 'b', 85, 88,null,null
union all select 'c', 77, 93,null,null
union all select 'd', 80, 90,null,null
union all select 'e', 99, 100,null,null
--创建存储过程
create proc mysql
as
update bb set zong=tt.zong,px=tt.px from tbl bb,
(SELECT name,zong , px=(SELECT COUNT(zong) FROM
(
select name,chinese,math,zong = chinese + math from tbl
) t
WHERE zong > a.zong or (zong = a.zong and chinese > a.chinese)) + 1
FROM
(
select name,chinese,math,zong = chinese + math from tbl
) a
) tt
where bb.name=tt.name
go
--调用存储过程
exec mysql
--查看
select * from tbl
--删除
drop table tbl
drop proc mysql
