一条检验SQL基础的语句!
发布日期:2021-10-21 03:38:05
浏览次数:23
分类:技术文章
本文共 2012 字,大约阅读时间需要 6 分钟。
数据表: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 ) studentsINNER 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 ) GroupsON 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转载地址:https://blog.csdn.net/WapWeb/article/details/23533 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
网站不错 人气很旺了 加油
[***.192.178.218]2024年04月08日 16时11分44秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
git学习:关于origin和master,自己增加一部分
2019-04-27
S3C6410驱动I80接口LCD
2019-04-27
note : Get FilePathName from FILE_OBJECT
2019-04-27
测试计算机端口是否开通的方法
2019-04-27
Chrome 91.0.4472.114 下载而不是打开PDF文件
2019-04-27
note : SSDT HOOK HOOK宏 转函数
2019-04-27
note : Get PID List
2019-04-27
Disk Opt
2019-04-27
note : Creates a hidden IE window
2019-04-27
note : COM Tips
2019-04-27
(ZT) setting a break in windbg when windows message queue have message which like WM_LBUTTONDOWN
2019-04-27
note : basic socket opt
2019-04-27
资料搜索 tips
2019-04-27
debug : 应用程序无法正常启动(0xc000007b)
2019-04-27
note : Change Environment by program
2019-04-27
vs2010 编译 TrueCrypt-7.1a-Source
2019-04-27
note : test Mutex
2019-04-27