SQLͳ¼Æ´óÈ«ÊÕ²Ø°æ ¸öÈËʹÓÃ

ÎÄÕÂÀ´Ô´£ºIT¹¤³Ì¼¼ÊõÍø£¬ È«ÎÄÁ´½Ó£ºhttp://www.systhinker.com/html/81/n-11481.html
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)t2where t1.stuid=t2.stuidorder 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) t2where 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) t2where 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) t2where 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 stuscoregroup by stuid,name order by ×Ü·Ödesc
8.Áгö¸÷Ãſγ̵Äƽ¾ù³É¼¨
select subject,avg(score) as avgscore from stuscoregroup 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 sc