学生の各課程の成績はSQL文の大全を統計します。

学生成績表：
名前：name
コース：subject
スコア：スコア
学号：stuid
張三
数学
89
1
張三
言語
80
1
張三
英語
70
1
李四
数学
90
2
李四
言語
70
2
李四
英語
80
2
テーブルを作成

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[stuscore](
    [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [score] [int] NULL,
    [stuid] [int] NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

問題：1.一人の総成績を計算して順位を付ける（表示フィールド：名前、総成績を要求する）2.一人の総成績を計算して順位を決める（表示フィールド： 学号、氏名、総成績）3.一人当たりの単科の最高成績を計算する（表示フィールドを要求する： 学号、氏名、課程、最高成績）4.一人当たりの平均成績を計算する（表示フィールドを要求する： 学号、名前、平均成績）5.各科目の成績が一番いい学生（表示フィールドを要求する： 学号、名前、科目、成績）6.各科目の成績が一番いい学生2人を並べています。 学号、氏名、科目、成績）  7.統計は以下の通りである
学号
名前
言語
数学
英語
合計点
平均点
 
 
 
 
 
 
 
8.各科目の平均成績（要求表示フィールド：課程、平均成績）9.数学の成績ランキング（要求表示フィールド：学名、成績、ランキング）10.数学の成績が2～3名の学生（表示フィールド：学名、名前、科目、成績）を表示する  11.李四の数学の成績の順位を求める12.統計は以下の通りである。
レッスン
不合格（0-59）個
良（60-80）個
優（81-100）個
 
 
 
 
13.統計は次の通りです。数学：張三（50分）、李四（90分）、王五（90分）、趙六（76分）答え：1.一人の総成績を計算して、順位を付けます。

select name,sum(score) as allscore from stuscore group by name order by allscore

2.一人当たりの総成績を計算して順位を決める

select distinct t1.name,t1.stuid,t2.allscore from  stuscore t1,
(
    select stuid,sum(score) as allscore from stuscore group by stuid
)t2
where t1.stuid=t2.stuid
order by t2.allscore desc

3. 各人単科の最高成績を計算する

select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
(
select stuid,max(score) as maxscore from stuscore group by stuid
) t2
where t1.stuid=t2.stuid and t1.score=t2.maxscore

4.一人当たりの平均成績を計算する

select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,
(
select stuid,avg(score) as avgscore from stuscore group by stuid
) t2
where t1.stuid=t2.stuid

5.各科目の成績が一番いい学生をリストアップする

select  t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,
(
select subject,max(score) as maxscore from stuscore group by subject
) t2
where t1.subject=t2.subject and t1.score=t2.maxscore

6.各科目の成績が一番いい学生二人を並べます。

select distinct t1.* from stuscore t1 
where t1.id in 
(select top 2 stuscore.id from stuscore where subject = t1.subject order by score desc) 
order by t1.subject

7.学号    名前    言語     数学     英語     合計点  平均点

select stuid as   ,name as   ,
sum(case when subject='  ' then score else 0 end) as   ,
sum(case when subject='  ' then score else 0 end) as   ,
sum(case when subject='  ' then score else 0 end) as   ,
sum(score) as   ,(sum(score)/count(*)) as    
from stuscore
group by stuid,name 
order by   desc

8.各科目の平均成績を一覧表示する

select subject,avg(score) as avgscore from stuscore
group by subject

9.数学の成績ランキングを並べる

declare @tmp table(pm int,name varchar(50),score int,stuid int)
insert into @tmp select null,name,score,stuid from stuscore where subject='  ' order by score desc
declare @id int
set @id=0;
update @tmp set @id=@id+1,pm=@id
select * from @tmp

 

select  DENSE_RANK () OVER(order by score desc) as row,name,subject,score,stuid from stuscore where subject='  '

order by score desc

10. 数学の成績は2-3名の学生を並べます。

select t3.*  from
(
select top 2 t2.*  from (
select top 3 name,subject,score,stuid from stuscore where subject='  '
order by score desc
) t2 order by t2.score
) t3 order by t3.score desc

11. 李四の数学の成績の順位を求めます。

declare @tmp table(pm int,name varchar(50),score int,stuid int)
insert into @tmp select null,name,score,stuid from stuscore where subject='  ' order by score desc
declare @id int
set @id=0;
update @tmp set @id=@id+1,pm=@id
select * from @tmp where name='  '

12. レッスン    不合格（-59）   良(-80)   優(-100)

select subject, 
(select count(*) from stuscore where score<60 and subject=t1.subject) as    ,
(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as  ,
(select count(*) from stuscore where score >80 and subject=t1.subject) as  
from stuscore t1 group by subject

13. 数学：張三（50分）、李四（90分）、王五（90分）、趙六（76分）

declare @s varchar(1000)
set @s=''
select @s =@s+','+name+'('+convert(varchar(10),score)+' )' from stuscore where subject='  ' 
set @s=stuff(@s,1,1,'')
print '  :'+@s