一条检验SQL基础的语句

王朝mssql·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

数据表:student_id,name,age,class_id,group_id

[学生id][姓名][年龄][班级id][组id]

用一条sql语句

1.求低于组平均年龄的人数比率在50%以下的那些组所在班级中高于班级平均年龄的学生

2.求组内最大年龄和最小年龄的学生既不是班级最大又不是班级最小的那些组所在班级的平均年龄

解法1:

SELECT students.name

FROM

(/*由学生集合向班级平均年龄集合进行一次投影,求出高于班级平均年龄的学生*/

SELECT students.name,students.class_id,students.group_id FROM students

INNER JOIN (/*此处求出现有学生所在的各个班级的平均年龄*/SELECT class_id,AVG(age) AS avgage FROM students GROUP BY class_id) students_1

ON students.class_id=students_1.class_id AND students.age>students_1.avgage

) students

INNER JOIN /*由高于班级平均年龄的学生集合向低于组平均年龄的人数比率在50%以下的那些组进行投影*/

(

SELECT students_0.group_id

FROM (/*现有学生所在组中每组的总人数*/select group_id,count(student_id) NumberOfGroup from students group by group_id) students_0

INNER JOIN

(/*学生集合向学生所在组的平均年龄集合进行一次投影,求出每组低于平均年龄的人数*/

SELECT students.group_id,COUNT(students.student_id) AS Number FROM students

INNER JOIN(SELECT group_id,AVG(age) AS avgage FROM students GROUP BY group_id) students_1

ON students.group_id=students_1.group_id AND students.age<students_1.avgage

GROUP BY students.group_id

) students_1

ON students_0.group_id=students_1.group_id

/*筛选出低于平均年龄人数比率在50%以下的那些组*/

where CAST(Number AS float)/CAST(NumberOfGroup AS float)<0.5

)

Groups

ON students.group_id=Groups.group_id

---------------------------------

解法2:

1.求低于组平均年龄的人数比率在50%以下的那些组所在班级中高于班级平均年龄的学生

Select * From school y Where age>(Select AVG(age) From school Where class_id=y.class_id) And class_id In (Select class_id From School x Where (Select Count(1) From school Where age<(Select AVG(age) From school Where group_id=x.group_id) And group_id=x.group_id)/(Select Count(1) From school Where group_id=x.group_id) <0.5)

2.求组内最大年龄和最小年龄的学生既不是班级最大又不是班级最小的那些组所在班级的平均年龄

Select class_id,AVG(age) From school x Where (Select Min(age) From school Where group_id=x.groupid And class_id=x.class_id)<>(Select Min(age) From school Where class_id=x.class_id) And (Select Max(age) From school Where group_id=x.groupid And class_id=x.class_id)<>(Select Max(age) From school Where class_id=x.class_id) Group By class_id

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航