SQL SERVER 交叉报表

最新推荐文章于 2021-08-13 14:25:20 发布

kongfanyu

最新推荐文章于 2021-08-13 14:25:20 发布

阅读量844

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/kongfanyu/article/details/6413892

本文档展示了如何从创建数据库开始，逐步建立表格并填充数据的过程。具体步骤包括：检查并删除已存在的test数据库，重新创建该数据库，并定义其存储位置及大小；接着创建学生表(student)和课程表(Course)，并插入示例数据；最后建立成绩表(Score)，关联学生表与课程表，并插入成绩数据。通过这些操作，演示了SQL语句在数据库管理中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if exists (select * from sysdatabases where name='test')
drop database test
go
create database test
on
(
name='test.mdf',
filename='E:/DataBase/test.mdf',
size=4
)
log on
(
name='test.ldf',
filename='E:/DataBase/test_log.ldf',
size=4
)
go
if exists (select * from sysobjects where name='student')
drop table student
go
create table student
(
SId int identity(1,1) primary key,
Sname varchar(20) not null
)
go
insert into student values ('郑绪栋')
insert into student values ('zhengxudong')
insert into student values ('AndyLau')
insert into student values ('绪栋')
select * from student
if exists (select * from sysobjects where name='Course')
drop table Course
go
create table Course
(
CId int identity(1,1) primary key,
Cname varchar(20) not null
)
go
insert into Course values ('语文')
insert into Course values ('数学')
insert into Course values ('英语')
insert into Course values ('音乐')
select * from Course
if exists (select * from sysobjects where name='Score')
drop table Score
go
create table Score
(
Id int identity(1,1) primary key,
Scid int not null,
Ssid int not null,
Score int not null,
constraint Fk_Score_Course_id foreign key (Scid) references Course (CId),
constraint Fk_Score_Student_id foreign key (Ssid) references Student (SId)
)
go
insert into Score values (1,1,80)
insert into Score values (1,2,83)
insert into Score values (1,3,75)
insert into Score values (1,4,90)

insert into Score values (3,1,70)
insert into Score values (2,2,80)
insert into Score values (3,3,75)
insert into Score values (2,4,90)

insert into Score values (2,1,65)
insert into Score values (2,2,65)
insert into Score values (3,3,75)
insert into Score values (3,4,85)

insert into Score values (4,1,88)
insert into Score values (4,2,80)
insert into Score values (4,3,75)
insert into Score values (4,4,95)

select * from Score
select * from Course
select * from student

--查询出各位同学各门功课的成绩
select (select s.Sname from student s where s.SId=Score.Ssid ) 姓名,
sum(case scid when 1 then score end) 语文,
sum(case scid when 2 then score end) 数学,
sum(case scid when 3 then score end) 英语,
sum(case scid when 4 then score end) 音乐
from score group by ssid