比较常用的sql 查询语句

create table SC

(

SNo int,

Sname nvarchar(50),

Cname nvarchar(50),

Score int,

)

insert into SC values

(1 , '张三', '数学', 89),

(1 ,'张三', '语文' ,80),

(1, '张三', '英语', 70),

(2, '李四', '数学', 90),

(2, '李四', '语文', 70),

(2, '李四', '英语', 80)

-- 计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

select Sname,SUM(Score)as SumScore from SC group by Sname order by SumScore

--计算每个人的总成绩并排名

select distinct t1.Sname, t1.SNo, t2.SumScore from SC t1,

(

select SNo, SUM(Score) as SumScore from SC group by SNo)t2

where t1.SNo = t2.SNo

--计算每个人单科的最高成绩

select t1.Sname,t1.Cname, t2.MaxScore from SC t1,

(select Sname, MAX(Score)as MaxScore from SC group by Sname)t2

where t1.Sname = t2.Sname and t1.Score = t2.MaxScore

-- 计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

select distinct t1.Sno, t1.Sname, t2.AvgScore from SC t1,

(select Sno,AVG(Score)as AvgScore from SC group by SNo)t2

where t1.SNo = t2.SNo

-- 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

select t1.Sno,t1.Sname,t2.Cname,t2.MaxScore from SC t1,

(select Cname, MAX(Score) as MaxScore from SC group by Cname)t2

where t1.Cname = t2.Cname and t2.MaxScore = t1.Score

--列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

select distinct t1.* from SC t1 where t1.SNo in

(select top 2 SNo from SC where t1.Cname = SC.Cname order by Score desc)

order by t1.Cname

-- 统计如下：学号姓名语文 数学 英语总分平均分

select sno,Sname,

SUM(case when cname = '语文' then score else 0 end) as 语文,

SUM(case when cname = '数学' then score else 0 end) as 数学,

SUM(case when cname = '英语' then score else 0 end) as 英语,

SUM(Score)as SumScore, AVG(score) as AvgScore

from SC

group by SNo,Sname

order by SumScore desc

-- 列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

select cname, AVG(score)as AvgSum from SC group by cname

--列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

declare @temp table(Sno int, Sname nvarchar(50), Score int, Sort int)

insert @temp select sno,sname,score,null from SC where SC.Cname='数学' order by Score desc

declare @id int

set @id=0;

update @temp set @id=@id +1, Sort = @id

select * from @temp

-- 列出数学成绩在-3名的学生（要求显示字段：学号，姓名,科目，成绩）

select top 2 t1.* from

(select top 3 * from sc where Cname='数学' order by Score)t1 order by t1.Score desc

-- 求出李四的数学成绩的排名

declare @temp1 table(Sno int, Sname nvarchar(50), Score int, Sort int)

insert @temp1 select sno,sname,score,null from SC where SC.Cname='数学' order by Score desc

declare @id1 int

set @id1=0;

update @temp1 set @id1=@id1 +1, Sort = @id1

select * from @temp1 where Sname = '李四'

-- 统计如下：课程不及格（-59）个 良（-80）个 优（-100）个

select cname,

(select COUNT(*) from SC where Score < 60 and Cname = t1.cname) as 不及格,

(select COUNT(*) from SC where Score between 60 and 80 and Cname = t1.cname) as 良,

(select COUNT(*) from SC where Score between 60 and 80 and Cname = t1.cname) as 优

from SC t1

group by t1.Cname

--统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)

declare @s varchar(1000)

set @s=''

select @s =@s+','+Sname+'('+convert(varchar(10),Score)+'分)' from sc where Cname='数学'

set @s=stuff(@s,1,1,'')

print '数学:'+@s