--> 测试数据: students
if object_id('students') is not null drop table students
create table students (id int,student_no varchar(7),student_name varchar(4))
insert into students select 1,'Y2T1601','张三'
insert into students select 2,'Y2T1602','李四'
insert into students select 3,'Y2T1603','王五'
insert into students select 4,'Y2T1604','赵六'--> 测试数据: subjects
if object_id('subjects') is not null drop table subjects
create table subjects (subject_id int,subject_name varchar(20))
insert into subjects select 1,'sql server'
insert into subjects select 2,'html'
insert into subjects select 3,'javascript'
insert into subjects select 4,'c#'
insert into subjects select 5,'java'--> 测试数据: scores
if object_id('scores') is not null drop table scores
create table scores (id int,student_no varchar(7),subject_id int,score int)
insert into scores select 1,'Y2T1601',1,67
insert into scores select 2,'Y2T1601',2,81
insert into scores select 3,'Y2T1601',3,74
insert into scores select 4,'Y2T1601',4,71
insert into scores select 5,'Y2T1601',5,83
insert into scores select 6,'Y2T1602',1,75
insert into scores select 7,'Y2T1602',2,72
insert into scores select 8,'Y2T1602',3,64
insert into scores select 9,'Y2T1602',4,90
insert into scores select 10,'Y2T1602',5,57
insert into scores select 11,'Y2T1603',1,83
insert into scores select 12,'Y2T1603',2,61
insert into scores select 13,'Y2T1603',3,46
insert into scores select 14,'Y2T1603',4,58
insert into scores select 15,'Y2T1603',5,78
insert into scores select 16,'Y2T1604',1,43
insert into scores select 17,'Y2T1604',2,71
insert into scores select 18,'Y2T1604',3,63
insert into scores select 19,'Y2T1604',4,95
insert into scores select 20,'Y2T1604',5,78select * from students
select * from subjects
select * from scores
/*要求
1、显示每个学生每科的分数,结果如:
student_name subject_name score
2、显示所有学生的总分,总分为所有科目分数的和,结果如:
student_name 总分
3、在第2题基础上。加上名次。并将最后结果按照总分升序排列。结果如:
student_name 总分 名次
*/
if object_id('students') is not null drop table students
create table students (id int,student_no varchar(7),student_name varchar(4))
insert into students select 1,'Y2T1601','张三'
insert into students select 2,'Y2T1602','李四'
insert into students select 3,'Y2T1603','王五'
insert into students select 4,'Y2T1604','赵六'--> 测试数据: subjects
if object_id('subjects') is not null drop table subjects
create table subjects (subject_id int,subject_name varchar(20))
insert into subjects select 1,'sql server'
insert into subjects select 2,'html'
insert into subjects select 3,'javascript'
insert into subjects select 4,'c#'
insert into subjects select 5,'java'--> 测试数据: scores
if object_id('scores') is not null drop table scores
create table scores (id int,student_no varchar(7),subject_id int,score int)
insert into scores select 1,'Y2T1601',1,67
insert into scores select 2,'Y2T1601',2,81
insert into scores select 3,'Y2T1601',3,74
insert into scores select 4,'Y2T1601',4,71
insert into scores select 5,'Y2T1601',5,83
insert into scores select 6,'Y2T1602',1,75
insert into scores select 7,'Y2T1602',2,72
insert into scores select 8,'Y2T1602',3,64
insert into scores select 9,'Y2T1602',4,90
insert into scores select 10,'Y2T1602',5,57
insert into scores select 11,'Y2T1603',1,83
insert into scores select 12,'Y2T1603',2,61
insert into scores select 13,'Y2T1603',3,46
insert into scores select 14,'Y2T1603',4,58
insert into scores select 15,'Y2T1603',5,78
insert into scores select 16,'Y2T1604',1,43
insert into scores select 17,'Y2T1604',2,71
insert into scores select 18,'Y2T1604',3,63
insert into scores select 19,'Y2T1604',4,95
insert into scores select 20,'Y2T1604',5,78select * from students
select * from subjects
select * from scores
/*要求
1、显示每个学生每科的分数,结果如:
student_name subject_name score
2、显示所有学生的总分,总分为所有科目分数的和,结果如:
student_name 总分
3、在第2题基础上。加上名次。并将最后结果按照总分升序排列。结果如:
student_name 总分 名次
*/
if object_id('students') is not null drop table students
create table students (id int,student_no varchar(7),student_name varchar(4))
insert into students select 1,'Y2T1601','张三'
insert into students select 2,'Y2T1602','李四'
insert into students select 3,'Y2T1603','王五'
insert into students select 4,'Y2T1604','赵六' --> 测试数据: subjects
if object_id('subjects') is not null drop table subjects
create table subjects (subject_id int,subject_name varchar(20))
insert into subjects select 1,'sql server'
insert into subjects select 2,'html'
insert into subjects select 3,'javascript'
insert into subjects select 4,'c#'
insert into subjects select 5,'java' --> 测试数据: scores
if object_id('scores') is not null drop table scores
create table scores (id int,student_no varchar(7),subject_id int,score int)
insert into scores select 1,'Y2T1601',1,67
insert into scores select 2,'Y2T1601',2,81
insert into scores select 3,'Y2T1601',3,74
insert into scores select 4,'Y2T1601',4,71
insert into scores select 5,'Y2T1601',5,83
insert into scores select 6,'Y2T1602',1,75
insert into scores select 7,'Y2T1602',2,72
insert into scores select 8,'Y2T1602',3,64
insert into scores select 9,'Y2T1602',4,90
insert into scores select 10,'Y2T1602',5,57
insert into scores select 11,'Y2T1603',1,83
insert into scores select 12,'Y2T1603',2,61
insert into scores select 13,'Y2T1603',3,46
insert into scores select 14,'Y2T1603',4,58
insert into scores select 15,'Y2T1603',5,78
insert into scores select 16,'Y2T1604',1,43
insert into scores select 17,'Y2T1604',2,71
insert into scores select 18,'Y2T1604',3,63
insert into scores select 19,'Y2T1604',4,95
insert into scores select 20,'Y2T1604',5,78 select * from students
select * from subjects
select * from scores select
a.student_name,b.subject_name,c.score
from
students a,subjects b,scores c
where
a.student_no=c.student_no
and
b.subject_id=c.subject_id
/*student_name subject_name score
------------ -------------------- -----------
张三 sql server 67
张三 html 81
张三 javascript 74
张三 c# 71
张三 java 83
李四 sql server 75
李四 html 72
李四 javascript 64
李四 c# 90
李四 java 57
王五 sql server 83
王五 html 61
王五 javascript 46
王五 c# 58
王五 java 78
赵六 sql server 43
赵六 html 71
赵六 javascript 63
赵六 c# 95
赵六 java 78(20 行受影响)*/
select a.student_name,b.subject_name,c.score
from students a
left join scores c on a.student_no=c.student_no
left join subjects b on b.subject_id=c.subject_id
/**
student_name subject_name score
------------ -------------------- -----------
张三 sql server 67
张三 html 81
张三 javascript 74
张三 c# 71
张三 java 83
李四 sql server 75
李四 html 72
李四 javascript 64
李四 c# 90
李四 java 57
王五 sql server 83
王五 html 61
王五 javascript 46
王五 c# 58
王五 java 78
赵六 sql server 43
赵六 html 71
赵六 javascript 63
赵六 c# 95
赵六 java 78(所影响的行数为 20 行)
**/--2
select a.student_name,sum(isnull(c.score,0)) as 总分
from students a
left join scores c on a.student_no=c.student_no
group by a.student_name
/**
student_name 总分
------------ -----------
李四 358
王五 326
张三 376
赵六 350(所影响的行数为 4 行)
**/--3
select *,名次=(select count(1)+1 from(
select a.student_name,sum(isnull(c.score,0)) as 总分
from students a
left join scores c on a.student_no=c.student_no
group by a.student_name) t2 where t2.总分>t1.总分)
from
(
select a.student_name,sum(isnull(c.score,0)) as 总分
from students a
left join scores c on a.student_no=c.student_no
group by a.student_name
) t1
order by 名次
/**
student_name 总分 名次
------------ ----------- -----------
张三 376 1
李四 358 2
赵六 350 3
王五 326 4(所影响的行数为 4 行)**/
--一
select r.student_name, h.subject_name, t.score
from students r join scores t
on r.student_no = t.student_no
join subjects h
on t.subject_id = h.subject_id
order by r.student_no
if object_id('students') is not null drop table students
create table students (id int,student_no varchar(7),student_name varchar(4))
insert into students select 1,'Y2T1601','张三'
insert into students select 2,'Y2T1602','李四'
insert into students select 3,'Y2T1603','王五'
insert into students select 4,'Y2T1604','赵六' --> 测试数据: subjects
if object_id('subjects') is not null drop table subjects
create table subjects (subject_id int,subject_name varchar(20))
insert into subjects select 1,'sql server'
insert into subjects select 2,'html'
insert into subjects select 3,'javascript'
insert into subjects select 4,'c#'
insert into subjects select 5,'java' --> 测试数据: scores
if object_id('scores') is not null drop table scores
create table scores (id int,student_no varchar(7),subject_id int,score int)
insert into scores select 1,'Y2T1601',1,67
insert into scores select 2,'Y2T1601',2,81
insert into scores select 3,'Y2T1601',3,74
insert into scores select 4,'Y2T1601',4,71
insert into scores select 5,'Y2T1601',5,83
insert into scores select 6,'Y2T1602',1,75
insert into scores select 7,'Y2T1602',2,72
insert into scores select 8,'Y2T1602',3,64
insert into scores select 9,'Y2T1602',4,90
insert into scores select 10,'Y2T1602',5,57
insert into scores select 11,'Y2T1603',1,83
insert into scores select 12,'Y2T1603',2,61
insert into scores select 13,'Y2T1603',3,46
insert into scores select 14,'Y2T1603',4,58
insert into scores select 15,'Y2T1603',5,78
insert into scores select 16,'Y2T1604',1,43
insert into scores select 17,'Y2T1604',2,71
insert into scores select 18,'Y2T1604',3,63
insert into scores select 19,'Y2T1604',4,95
insert into scores select 20,'Y2T1604',5,78 --select * from students
--select * from subjects
--select * from scores
select
student_name,sum(score) as score
from
(
select
a.student_name,b.subject_name,c.score
from
students a,subjects b,scores c
where
a.student_no=c.student_no
and
b.subject_id=c.subject_id)t
group by
student_name
/*student_name score
------------ -----------
李四 358
王五 326
张三 376
赵六 350(4 行受影响)*/
select r.student_name, sum(t.score) as 总分
from students r join scores t
on r.student_no = t.student_no
group by r.student_name
if object_id('students') is not null drop table students
create table students (id int,student_no varchar(7),student_name varchar(4))
insert into students select 1,'Y2T1601','张三'
insert into students select 2,'Y2T1602','李四'
insert into students select 3,'Y2T1603','王五'
insert into students select 4,'Y2T1604','赵六' --> 测试数据: subjects
if object_id('subjects') is not null drop table subjects
create table subjects (subject_id int,subject_name varchar(20))
insert into subjects select 1,'sql server'
insert into subjects select 2,'html'
insert into subjects select 3,'javascript'
insert into subjects select 4,'c#'
insert into subjects select 5,'java' --> 测试数据: scores
if object_id('scores') is not null drop table scores
create table scores (id int,student_no varchar(7),subject_id int,score int)
insert into scores select 1,'Y2T1601',1,67
insert into scores select 2,'Y2T1601',2,81
insert into scores select 3,'Y2T1601',3,74
insert into scores select 4,'Y2T1601',4,71
insert into scores select 5,'Y2T1601',5,83
insert into scores select 6,'Y2T1602',1,75
insert into scores select 7,'Y2T1602',2,72
insert into scores select 8,'Y2T1602',3,64
insert into scores select 9,'Y2T1602',4,90
insert into scores select 10,'Y2T1602',5,57
insert into scores select 11,'Y2T1603',1,83
insert into scores select 12,'Y2T1603',2,61
insert into scores select 13,'Y2T1603',3,46
insert into scores select 14,'Y2T1603',4,58
insert into scores select 15,'Y2T1603',5,78
insert into scores select 16,'Y2T1604',1,43
insert into scores select 17,'Y2T1604',2,71
insert into scores select 18,'Y2T1604',3,63
insert into scores select 19,'Y2T1604',4,95
insert into scores select 20,'Y2T1604',5,78 --select * from students
--select * from subjects
--select * from scores
select *,名次=row_number()over(order by score)
from
(
select
student_name,sum(score) as score
from
(
select
a.student_name,b.subject_name,c.score
from
students a,subjects b,scores c
where
a.student_no=c.student_no
and
b.subject_id=c.subject_id)t
group by
student_name)t
/*student_name score 名次
------------ ----------- --------------------
王五 326 1
赵六 350 2
李四 358 3
张三 376 4(4 行受影响)*/
--三
select *,
(select count(1) from (
select r.student_name, sum(t.score) as 总分
from students r join scores t
on r.student_no = t.student_no
group by r.student_name
) k where 总分 >= h.总分) as 名次
from
(
select r.student_name, sum(t.score) as 总分
from students r join scores t
on r.student_no = t.student_no
group by r.student_name
) h
order by 总分
---------------------
王五 326 4
赵六 350 3
李四 358 2
张三 376 1
select r.student_name, sum(t.score) as 总分
from students r join scores t
on r.student_no = t.student_no
group by r.student_name
用一个临时表代替 你就会明白了