1.查找所有科目均及格（>=60分）的同学并列出其各科分数；
SELECT t1.id,t1.score AS chinese,t2.score AS math,t3.score AS english FROM chinese t1,math t2,english t3
WHERE t1.id = t2.id AND t2.id=t3.id AND t1.score>=60 AND t2.score >=60 AND t3.score>=60;
2.查找所有同学的总分（没有分数的科目按照0分计算）和总分名次，并按照名次进行排序；
SET @rankid = 0;
SELECT t2.*,@rankid:=@rankid+1 AS rankid FROM ( SELECT id ,SUM(score) as total FROM ( SELECT id,score FROM chinese UNION select id,score from math UNION select id,score from english ) t1 GROUP BY id ORDER BY total DESC ) t2;
3.查找三个学科分数的平均数，中位数和众数。
#平均数
SELECT 'chinese' AS subject, truncate(AVG(score),1) as avg FROM chinese UNION SELECT 'math', truncate(AVG(score),1) FROM math UNION SELECT 'english',truncate(AVG(score),1) FROM english ;
#中位数
SET @chiId=0; SET @mathId = 0; SET @engId = 0; SELECT "chinese" as subject ,avg(score) median FROM ( SELECT score,@chiId:=@chiId+1 AS rankid,(SELECT COUNT(*) FROM chinese) AS count FROM chinese ORDER BY score ) t WHERE rankid IN (COUNT/2 ,COUNT/2+1,(COUNT+1)/2) UNION SELECT "math" ,AVG(score) FROM ( SELECT score,@mathId:=@mathId+1 AS rankid,(SELECT COUNT(*) FROM math) AS count FROM math ORDER BY score ) t WHERE rankid IN (COUNT/2 ,COUNT/2+1,(COUNT+1)/2) UNION SELECT "english" ,AVG(score) FROM ( SELECT score,@engId:=@engId+1 AS rankid,(SELECT COUNT(*) FROM english) AS count FROM english ORDER BY score ) t WHERE rankid IN (COUNT/2 ,COUNT/2+1,(COUNT+1)/2);
#中位数
SELECT "chinese" as subject,AVG(score) AS median FROM (SELECT a.score FROM chinese a,chinese b GROUP BY a.score HAVING SUM(case when a.score=b.score then 1 ELSE 0 END ) >=abs(SUM(SIGN(a.score-b.score))) ) c UNION SELECT "math" as subject,AVG(score) AS median FROM (SELECT a.score FROM math a,math b GROUP BY a.score HAVING SUM(case when a.score=b.score then 1 ELSE 0 END ) >=abs(SUM(SIGN(a.score-b.score))) ) c UNION SELECT "english" as subject,AVG(score) AS median FROM (SELECT a.score FROM english a,english b GROUP BY a.score HAVING SUM(case when a.score=b.score then 1 ELSE 0 END ) >=abs(SUM(SIGN(a.score-b.score))) ) c
#众数
SELECT "chinese" subject,score, COUNT(1) COUNT from chinese GROUP BY score having count = ( SELECT COUNT(1) as count FROM chinese GROUP BY score ORDER BY count DESC LIMIT 1 ) union SELECT "math", score, COUNT(1) count from math GROUP BY score having count = ( SELECT COUNT(1) AS count FROM math GROUP BY score ORDER BY count DESC LIMIT 1 ) union SELECT "english",score, COUNT(1) count from english GROUP BY score having count = ( SELECT COUNT(1) AS count FROM english GROUP BY score ORDER BY count DESC LIMIT 1 );
数据如下：
chinene
id score
001 65
002 56
004 76
005 37
006 34
007 84
008 93
009 84
010 32
011 57
