头大

mansioncool 2009-05-22 04:00:33
学生成绩信息三个表,结构如下:
学生表:Member
字段名称 数据类型 说明
MID Char(10) 学生号,主键
MName Char(50) 姓名
课程表:
字段名称 数据类型 说明
FID Char(10) 课程,主键
FName Char(50) 课程名
成绩表:Score
字段名称 数据类型 说明
SID int 自动编号,主键,成绩记录号
FID Char(10) 课程号,外键
MID Char(10) 学生号,外键
Score int 成绩
请编写T-SQL语句来实现如下功能:
1) 查询各个学生语文、数学、英语、历史课程成绩,例如下表:
姓名 语文 数学 英语 历史
张萨 78 67 89 76
王强 89 67 84 96
李三 70 87 92 56
李四 80 78 97 66
2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
3) 统计各个学生参加考试课程的平均分,且按平均分数由高到底排序。
4) 创建存储过程,分别查询参加1、2、3、4门考试及没有参加考试的学生名单,要求显示姓名、学号。
主要是想要实现第一题的那个样式 始终实现不了 各位大神能帮帮小弟不
...全文
205 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
mansioncool 2009-05-22
  • 打赏
  • 举报
回复
我这个提示是3个表的呀 实在不知道怎么弄
mansioncool 2009-05-22
  • 打赏
  • 举报
回复
select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
这块我不理解是什么意思 能解释一下吗? 学得少啊
pt1314917 2009-05-22
  • 打赏
  • 举报
回复
2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
3) 统计各个学生参加考试课程的平均分,且按平均分数由高到底排序。
4) 创建存储过程,分别查询参加1、2、3、4门考试及没有参加考试的学生名单,要求显示姓名、学号。
主要是想要实现第一题的那个样式 始终实现不了 各位大神能帮帮小弟不




--4、
输入什么参数,查询什么结果?
pt1314917 2009-05-22
  • 打赏
  • 举报
回复

--3、这样?
select * from
(select 课程名=fname,平均分=avg(score) from 课程表 a,成绩表 b where a.fid=b.fid group by fname)a
order by 平均分 desc

JonasFeng 2009-05-22
  • 打赏
  • 举报
回复
楼主看看1楼的,就知道了。
多么详细呀。

pt1314917 2009-05-22
  • 打赏
  • 举报
回复

--2、
select Mname,fname,score from 学生表 a,课程表 b,成绩表 c where a.MID=c.MID and b.fid=c.fid and c.score<70


pt1314917 2009-05-22
  • 打赏
  • 举报
回复


--1、行转列
declare @sql varchar(8000)
set @sql='select 姓名=b.Mname'
select @sql=@sql+',['+FName+']=max(case FID when '''+ltrim(FID)+''' then score else 0 end)'
from 课程表
set @sql=@sql+' from 成绩表 a,学生表 b where a.MID=b.MID group by b.Mname'
百年树人 2009-05-22
  • 打赏
  • 举报
回复
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂

整理人:中国风(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 行受影响)
*/

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧