--查询地理课比生物课成绩高的学生信息

SELECT a.StuId,a.StuName, b.CourseId,b.CourseName,c.Score,e.CourseId,e.CourseName,d.Score
FROM tblStudent a, tblCourse b,tblScore c,tblScore d,tblCourse e
WHERE a.StuId = c.StuId AND b.CourseId = c.CourseId AND d.CourseId = e.CourseId
	AND b.CourseName <> e.CourseName AND a.StuId = d.StuId AND e.CourseId = d.CourseId
	AND b.CourseName = '地理' AND e.CourseName = '生物' AND c.Score > d.Score
	

SELECT a.StuId, a.StuName
FROM tblStudent a
WHERE 
	(SELECT b.Score FROM tblScore b WHERE b.StuId = a.StuId AND b.CourseId = (SELECT d.CourseId FROM tblCourse d WHERE d.CourseName = '地理')) 
		> 
	(SELECT c.Score FROM tblScore c WHERE c.StuId = a.StuId AND c.CourseId = (SELECT d.CourseId FROM tblCourse d WHERE d.CourseName = '生物'))


--查询平均成绩大于75分的学生学号和平均成绩
SELECT StuId,avg(Score)
FROM tblScore
GROUP BY StuId 
HAVING avg(Score) > 75

--查询所有学生的学号、姓名、选课数、总成绩
SELECT d.stuId, e.StuName, d.courses, d.scores
FROM 
(SELECT a.StuId 'StuId', count(b.CourseName) 'courses',count(c.Score) 'scores'
FROM tblStudent a, tblCourse b, tblScore c
WHERE a.StuId = c.StuId AND b.CourseId = c.CourseId
GROUP BY a.StuId) d, tblStudent e
WHERE d.StuId = e.StuId

--查询没有学过徐小明老师课程的学生
SELECT a.StuId,a.StuName,b.CourseName,b.TeaId, d.TeaName
FROM tblStudent a, tblCourse b,tblTeacher d
WHERE b.TeaId <> (SELECT c.TeaId FROM tblTeacher c WHERE c.TeaName = '徐小明')
	AND d.TeaId <> (SELECT c.TeaId FROM tblTeacher c WHERE c.TeaName = '徐小明')
	AND b.TeaId = d.TeaId
ORDER BY a.StuId,b.CourseId

--sql server分页查询
SELECT * FROM tblCourse
--每页显示2条记录，查询第一页，(1-1)*2 = 0
SELECT TOP 2 * 
FROM tblCourse 
WHERE CourseId NOT IN(SELECT TOP 0 CourseId 
				FROM tblCourse 
				ORDER BY CourseId ) 
ORDER BY CourseId 

--每页显示2条记录，查询第二页，(2-1)*2 = 2
SELECT TOP 2 * 
FROM tblCourse 
WHERE CourseId NOT IN(SELECT TOP 2 CourseId 
				FROM tblCourse 
				ORDER BY CourseId ) 
ORDER BY CourseId

--每页显示2条记录，查询第三页, (3-1)*2 = 4
SELECT TOP 2 * 
FROM tblCourse 
WHERE CourseId NOT IN(SELECT TOP 4 CourseId 
				FROM tblCourse 
				ORDER BY CourseId ) 
ORDER BY CourseId 

--每页显示2条记录，查询第四页, (4-1)*2 = 6
SELECT TOP 2 * 
FROM tblCourse 
WHERE CourseId NOT IN(SELECT TOP 6 CourseId 
				FROM tblCourse 
				ORDER BY CourseId ) 
ORDER BY CourseId 

--每页显示2条记录，查询第五页, (5-1)*2 = 8
SELECT TOP 2 * 
FROM tblCourse 
WHERE CourseId NOT IN(SELECT TOP 8 CourseId 
				FROM tblCourse 
				ORDER BY CourseId ) 
ORDER BY CourseId 

--查询各科成绩前三名的记录


SELECT t.字段名
FROM (SELECT 字段（可以是多表关联的字段） ,row_number() over(order by 某个排序字段 desc) r 
	  FROM 表名 
	  where 过滤条件 ) t 
where t.r <= (每页显示条数*要查询的页) 
	and t.r > (每页显示条数*(要查询的页-1))
	
--sql server 2005 及以上版本的sql分页查询
--每页显示2条记录，查询第1页
SELECT t.CourseId,t.CourseName, t.TeaId
FROM (SELECT a.*,row_number() over(order by CourseId) r 
	  FROM tblCourse a) t 
where t.r <= (2*1) 
	and t.r > (2*(1-1))

--每页显示2条记录，查询第2页
SELECT t.CourseId,t.CourseName, t.TeaId
FROM (SELECT a.*,row_number() over(order by CourseId) r 
	  FROM tblCourse a) t 
where t.r <= (2*2) 
	and t.r > (2*(2-1))