UC知道问道简单的SQL题
来源：百度知道 编辑：UC知道 时间：2024/05/10 13:10:10
现在有几个问题,每个问题只用一句sql语句解答.
1.列出所有不及格(mark<60)的学生姓名
2.列出所有超过一门学科不及格的学生姓名
3.列出所有学科都不及格的学生姓名
4.列出mark排名前三(注意:包括并列)的学生姓名
5.列出总分排名前三(包括并列)的学生名字
6.每门学科最高分数的获得者名字及分数(包括并列)
很简单吧.
1.select name from table where mark<60
2.select name from (select name,count(name) AS countx from table where mark<60 group by name) DERIVEDTBL where countx>1
3.SELECT a.name FROM (SELECT name, COUNT(name) AS countx FROM table WHERE mark< 60 GROUP BY name) a INNER JOIN (SELECT name, COUNT(xueke) AS countxueke FROM table GROUP BY name) b ON a.name1 = b.name1 AND a.countx = b.countxueke
4.SELECT table.name FROM table INNER JOIN (SELECT TOP 3 mark FROM table GROUP BY mark ORDER BY mark DESC) a ON table.mark = a.mark ORDER BY a.mark DESC
5.SELECT a.name FROM (SELECT name, SUM(mark) AS zongchengji FROM tablei GROUP BY name) a INNER JOIN (SELECT TOP 3 zongchengji FROM (SELECT SUM(mark) AS zongchengji FROM table GROUP BY name) DERIVEDTBL GROUP BY zongchengji ORDER BY zongchengji DESC) b ON a.zongchengji = b.zongchengji ORDER BY b.zongchengji DESC
6.SELECT table.name, table.mark FROM table INNER JOIN (SELECT lesson, MAX(mar