这个是别人设计的数据库，看起来比较费劲，请大家帮忙写一下。create table t_Card_Grade --(年级级表)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
sGradeName varchar(255) null, --类型名称
)
create table t_Card_Class --(班级表)
(
ID int not null, --自动编号
nGradeID int null, --级次ID
nSchoolID int null, --学校ID
sClassName varchar(255) null, --类型名称
)
create table t_Stu --(学生信息表)
(
ID int IDENTITY(1, 1) not null, --自动编号
name varchar(50) null, --名称
nClassid int null, --班级ID
nSchoolID int null --学校ID
)
create table t_Card_Exam --(考试表)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
sExamName varchar(255) null, --类型名称
)
create table t_Card_Course --(课程表)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
sCourseName varchar(255) null, --类型名称
)
create table t_Card_ExamCou --(考试课程表)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
nExamID int null, --考试号
nCourseID int null, --课程号
nClassID int null, --班级号
nTotalScore int null, --课程总分
)
create table t_Card_Score --(成绩表)
(
ID int IDENTITY(1, 1) not null, --自动编号
nSchoolID int not null, --学校ID
nCouExamID int null, --考试号
nStuID int null, --学生ID
deScore decimal(9,1) null --考试分数
)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
sGradeName varchar(255) null, --类型名称
)
create table t_Card_Class --(班级表)
(
ID int not null, --自动编号
nGradeID int null, --级次ID
nSchoolID int null, --学校ID
sClassName varchar(255) null, --类型名称
)
create table t_Stu --(学生信息表)
(
ID int IDENTITY(1, 1) not null, --自动编号
name varchar(50) null, --名称
nClassid int null, --班级ID
nSchoolID int null --学校ID
)
create table t_Card_Exam --(考试表)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
sExamName varchar(255) null, --类型名称
)
create table t_Card_Course --(课程表)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
sCourseName varchar(255) null, --类型名称
)
create table t_Card_ExamCou --(考试课程表)
(
ID int not null, --自动编号
nSchoolID int not null, --学校ID
nExamID int null, --考试号
nCourseID int null, --课程号
nClassID int null, --班级号
nTotalScore int null, --课程总分
)
create table t_Card_Score --(成绩表)
(
ID int IDENTITY(1, 1) not null, --自动编号
nSchoolID int not null, --学校ID
nCouExamID int null, --考试号
nStuID int null, --学生ID
deScore decimal(9,1) null --考试分数
)
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
好象上次已经给你写了个大概的,你照着改改就行了,如果不行
请给出表结构,所有的测试数据,相关算法和需要的结果.谢谢!http://topic.csdn.net/u/20090114/16/46192222-f7e9-44ff-b562-7b0b5b0ae13e.html?seed=260859932--总分重复(相同)时保留名次空缺
create table tb(name varchar(10), grade int, banji int , kemu varchar(10), score int)
insert into tb values('A' , 1 , 1 , '语文' , 100)
insert into tb values('A' , 1 , 1 , '数学' , 100)
insert into tb values('A' , 1 , 1 , '英语' , 100)
insert into tb values('B' , 1 , 1 , '语文' , 99)
insert into tb values('B' , 1 , 1 , '数学' , 98)
insert into tb values('B' , 1 , 1 , '英语' , 97)
insert into tb values('C' , 1 , 1 , '语文' , 96)
insert into tb values('C' , 1 , 1 , '数学' , 95)
insert into tb values('C' , 1 , 1 , '英语' , 94)
insert into tb values('D' , 1 , 2 , '语文' , 100)
insert into tb values('D' , 1 , 2 , '数学' , 100)
insert into tb values('D' , 1 , 2 , '英语' , 100)
insert into tb values('E' , 1 , 2 , '语文' , 50)
insert into tb values('E' , 1 , 2 , '数学' , 40)
insert into tb values('E' , 1 , 2 , '英语' , 30)
godeclare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , max(case kemu when ''' + kemu + ''' then score else 0 end) [' + kemu + ']'
from (select distinct kemu from tb) as a
set @sql = @sql + ' , sum(score) 总分 from tb group by name'exec('select t1.* , t2.px 班级名次 , t3.px 年纪名次 from (' + @sql + ') t1 ,
(
select m.* , px = (select count(score) from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) n where n.grade = m.grade and n.banji = m.banji and n.score > m.score) + 1 from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) m
) t2,
(
select m.* , px = (select count(score) from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) n where n.grade = m.grade and n.score > m.score) + 1 from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) m
) t3
where t1.name = t2.name and t1.name = t3.name
') drop table tb/*
name 数学 英语 语文 总分 班级名次 年纪名次
---------- ----------- ----------- ----------- ----------- ----------- -----------
A 100 100 100 300 1 1
B 98 97 99 294 2 3
C 95 94 96 285 3 4
D 100 100 100 300 1 1
E 40 30 50 120 2 5*/
--总分重复(相同)时合并名次
create table tb(name varchar(10), grade int, banji int , kemu varchar(10), score int)
insert into tb values('A' , 1 , 1 , '语文' , 100)
insert into tb values('A' , 1 , 1 , '数学' , 100)
insert into tb values('A' , 1 , 1 , '英语' , 100)
insert into tb values('B' , 1 , 1 , '语文' , 99)
insert into tb values('B' , 1 , 1 , '数学' , 98)
insert into tb values('B' , 1 , 1 , '英语' , 97)
insert into tb values('C' , 1 , 1 , '语文' , 96)
insert into tb values('C' , 1 , 1 , '数学' , 95)
insert into tb values('C' , 1 , 1 , '英语' , 94)
insert into tb values('D' , 1 , 2 , '语文' , 100)
insert into tb values('D' , 1 , 2 , '数学' , 100)
insert into tb values('D' , 1 , 2 , '英语' , 100)
insert into tb values('E' , 1 , 2 , '语文' , 50)
insert into tb values('E' , 1 , 2 , '数学' , 40)
insert into tb values('E' , 1 , 2 , '英语' , 30)
godeclare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , max(case kemu when ''' + kemu + ''' then score else 0 end) [' + kemu + ']'
from (select distinct kemu from tb) as a
set @sql = @sql + ' , sum(score) 总分 from tb group by name'exec('select t1.* , t2.px 班级名次 , t3.px 年纪名次 from (' + @sql + ') t1 ,
(
select m.* , px = (select count(distinct score) from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) n where n.grade = m.grade and n.banji = m.banji and n.score > m.score) + 1 from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) m
) t2,
(
select m.* , px = (select count(distinct score) from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) n where n.grade = m.grade and n.score > m.score) + 1 from
(
select name , grade , banji , sum(score) score from tb group by name , grade , banji
) m
) t3
where t1.name = t2.name and t1.name = t3.name
') drop table tb/*
name 数学 英语 语文 总分 班级名次 年纪名次
---------- ----------- ----------- ----------- ----------- ----------- -----------
A 100 100 100 300 1 1
B 98 97 99 294 2 2
C 95 94 96 285 3 3
D 100 100 100 300 1 1
E 40 30 50 120 2 4
*/