日期:2014-05-18 浏览次数:20708 次
-- 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
------解决方案--------------------
1、创建一个表,表结构如下:
学号char (10),姓名 varchar(8),入学日期 char(10) --备注:格式为yyyy-mm-dd
问题:如何使得 Char类型的 入学日期 格式约束为 YYYY-MM-DD ???
create table tb(学号 char(10),姓名 varchar(8),入学日期 datetime)
2、写一存储过程
把成绩表中字段 chinese ,math 求和,并把和值写入到总分字段中;
按如下规则:先总分排名,若总分一样 则按语文成绩排名,如语文成绩一样,则名次相同,把名次写入名次字段中。
如:姓名 语文 数学 总分 名次
张三 98 90 ? ?
李四 100 88 ? ?
SELECT * , 名次=(SELECT COUNT(总分) FROM
(
select 姓名,语文,数学,总分 = 语文 + 数学 from tb
) t
WHERE 总分 > a.总分 or (总分 = a.总分 and 语文 > a.语文)) + 1
FROM
(
select 姓名,语文,数学,总分 = 语文 + 数学 from tb
) a
ORDER BY 名次
------解决方案--------------------
第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
select * from #tc
select *,total=chinese + math,rank=(select count(1)+1 from #tc b where b.chinese + b.math>a.chinese + a.math ) from #tc a order by rank
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
------解决方案--------------------
收下了