您当前的位置：首页 > sql

SQL练习73：考试分数（二）

Dream丶Killer 发布时间：2021-02-15 16:16:49 ，浏览量：4

SQL练习73：考试分数（二）

题目链接：牛客网

题目描述牛客每次考试完，都会有一个成绩表(grade)，如下: 在这里插入图片描述第1行表示用户id为1的用户选择了C++岗位并且考了11001分 … 第8行表示用户id为8的用户选择了前端岗位并且考了9999分

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下: 在这里插入图片描述

解法先获取GROUP BY分组获得各个job的平均分数avg，再将结果与grade表连接起来。查找avg


    
        
            
                
                
                    Dream丶Killer
                    暂无认证
                
            
            
                
                    
                        4浏览
                        0关注
                        154博文
                        0收益
                    

                    
                        0浏览
                        0点赞
                        0打赏
                        0留言
                    
                
            
            
                私信
                关注
            

        
        
            热门博文
            
                一行Python代码，畅玩童年经典游戏~
（成功解决）Python连接clickhouse
Pandas玩转数据透视表，用它就够了~
Pyinstaller+Pipenv打包Python文件，简直不要太香~
tableau每日一技：将标签显示在条形图的柱子上
Python字典：还有我不知道高阶玩法？
爬虫福音：Github星标14K+，一个开源的IP代理池
pathlib一个优雅的路径操作模块，碾压os
数据分析实战 | A/B测试探寻哪种广告点击率更高？
4K壁纸批量采集，张张精品，全程自动化






    [ 申请 ]友情链接：
    
        快连
        快连vpn
        搜外友链
        笔趣阁
        爱思助手
        ClashX教程
        绘画宝宝
        配音宝宝
    


    
        
            关于我们
            服务条款
            广告服务
            联系我们
            网站地图
            免责声明
            WAP
        
        技术支持：
            武汉快勤科技有限公司
            XML网站地图 
            备案号：鄂ICP备18027844号-9
            
        
    




    
        立即登录/注册
        
    
    
        
        微信扫码登录
    












	    基本
        文件
        流程
        错误
        SQL
        调试
    

		    
    
	请求信息 : 2025-08-09 07:53:19 HTTP/2.0 GET : /home/article/detail/id/435798.html
运行时间 : 0.0484s ( Load:0.0125s Init:0.0013s Exec:0.0227s Template:0.0121s )
吞吐率 : 20.66req/s
内存开销 : 1,927.42 kb
查询信息 : 18 queries 0 writes 
文件加载 : 36
缓存信息 : 5 gets 0 writes 
配置加载 : 132
会话信息 : SESSION_ID=po83updeor6saf62vf6bu7vqi0
    
    
        
    
	/www/wwwroot/www.chaojiit.com/index.php ( 1.30 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/ThinkPHP.php ( 4.71 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Think.class.php ( 12.32 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Storage.class.php ( 1.38 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Storage/Driver/File.class.php ( 3.56 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Mode/common.php ( 2.82 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Common/functions.php ( 51.07 KB )
/www/wwwroot/www.chaojiit.com/Application/Common/Common/function.php ( 6.83 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Hook.class.php ( 4.02 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/App.class.php ( 12.44 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Dispatcher.class.php ( 15.15 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Route.class.php ( 13.38 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Controller.class.php ( 10.95 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/View.class.php ( 7.96 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Behavior/BuildLiteBehavior.class.php ( 3.69 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Behavior/ParseTemplateBehavior.class.php ( 3.89 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Behavior/ContentReplaceBehavior.class.php ( 1.93 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Conf/convention.php ( 11.18 KB )
/www/wwwroot/www.chaojiit.com/Application/Common/Conf/config.php ( 1.81 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Lang/zh-cn.php ( 2.57 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Conf/debug.php ( 1.51 KB )
/www/wwwroot/www.chaojiit.com/Application/Home/Conf/config.php ( 0.05 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Behavior/ReadHtmlCacheBehavior.class.php ( 5.62 KB )
/www/wwwroot/www.chaojiit.com/Application/Home/Controller/ArticleController.class.php ( 6.55 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Model.class.php ( 67.27 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Db.class.php ( 5.70 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Db/Driver/Mysql.class.php ( 8.73 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Db/Driver.class.php ( 41.60 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Cache.class.php ( 3.84 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Cache/Driver/File.class.php ( 5.90 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Template.class.php ( 28.35 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Template/TagLib/Cx.class.php ( 22.62 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Template/TagLib.class.php ( 9.19 KB )
/www/wwwroot/www.chaojiit.com/Application/Runtime/Cache/Home/3c8a1a47a3534a7b1252c226abfc3928.php ( 14.50 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Behavior/WriteHtmlCacheBehavior.class.php ( 1.43 KB )
/www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Behavior/ShowPageTraceBehavior.class.php ( 5.27 KB )
    
    
        
    
	[ app_init ] --START--
Run Behavior\BuildLiteBehavior [ RunTime:0.000011s ]
[ app_init ] --END-- [ RunTime:0.000047s ]
[ app_begin ] --START--
Run Behavior\ReadHtmlCacheBehavior [ RunTime:0.000567s ]
[ app_begin ] --END-- [ RunTime:0.000602s ]
[ view_parse ] --START--
[ template_filter ] --START--
Run Behavior\ContentReplaceBehavior [ RunTime:0.000075s ]
[ template_filter ] --END-- [ RunTime:0.000108s ]
Run Behavior\ParseTemplateBehavior [ RunTime:0.009032s ]
[ view_parse ] --END-- [ RunTime:0.009071s ]
[ view_filter ] --START--
Run Behavior\WriteHtmlCacheBehavior [ RunTime:0.000290s ]
[ view_filter ] --END-- [ RunTime:0.000386s ]
[ app_end ] --START--
    
    
        
    
	[2] session_save_path(): open_basedir restriction in effect. File(/var/lib/php/session) is not within the allowed path(s): (/www/wwwroot/www.chaojiit.com/:/tmp/) /www/wwwroot/www.chaojiit.com/ThinkPHP/Common/functions.php 第 1239 行.
[8192] Array and string offset access syntax with curly braces is deprecated /www/wwwroot/www.chaojiit.com/ThinkPHP/Library/Think/Cache/Driver/File.class.php 第 59 行.
[8] Undefined variable: user /www/wwwroot/www.chaojiit.com/Application/Runtime/Cache/Home/3c8a1a47a3534a7b1252c226abfc3928.php 第 37 行.
[8] Undefined variable: user /www/wwwroot/www.chaojiit.com/Application/Runtime/Cache/Home/3c8a1a47a3534a7b1252c226abfc3928.php 第 98 行.
[8] Trying to access array offset on value of type null /www/wwwroot/www.chaojiit.com/Application/Runtime/Cache/Home/3c8a1a47a3534a7b1252c226abfc3928.php 第 98 行.
[8] Undefined variable: user /www/wwwroot/www.chaojiit.com/Application/Runtime/Cache/Home/3c8a1a47a3534a7b1252c226abfc3928.php 第 99 行.
[8] Trying to access array offset on value of type null /www/wwwroot/www.chaojiit.com/Application/Runtime/Cache/Home/3c8a1a47a3534a7b1252c226abfc3928.php 第 99 行.
[8] Undefined variable: pinglun_list /www/wwwroot/www.chaojiit.com/Application/Runtime/Cache/Home/3c8a1a47a3534a7b1252c226abfc3928.php 第 108 行.
    
    
        
    
	SHOW COLUMNS FROM `configuration` [ RunTime:0.0005s ]
SELECT `value` FROM `configuration` WHERE `name` = 'site_name' LIMIT 1   [ RunTime:0.0002s ]
SHOW COLUMNS FROM `menu` [ RunTime:0.0007s ]
SELECT * FROM `menu` WHERE `fid` = 0 AND `status` = 1  [ RunTime:0.0002s ]
SELECT * FROM `menu` WHERE `fid` = 1 AND `status` = 1  [ RunTime:0.0001s ]
SELECT * FROM `menu` WHERE `fid` = 2 AND `status` = 1  [ RunTime:0.0001s ]
SELECT * FROM `menu` WHERE `fid` = 3 AND `status` = 1  [ RunTime:0.0001s ]
SELECT * FROM `menu` WHERE `fid` = 4 AND `status` = 1  [ RunTime:0.0001s ]
SHOW COLUMNS FROM `article` [ RunTime:0.0004s ]
SELECT * FROM `article` WHERE `id` = 435798 LIMIT 1   [ RunTime:0.0007s ]
SHOW COLUMNS FROM `bloger` [ RunTime:0.0006s ]
SELECT * FROM `bloger` WHERE `id` = 783 LIMIT 1   [ RunTime:0.0002s ]
SELECT COUNT(*) AS tp_count FROM `article` WHERE `bloger_id` = 783 LIMIT 1   [ RunTime:0.0001s ]
SHOW COLUMNS FROM `article_content` [ RunTime:0.0004s ]
SELECT `content` FROM `article_content` WHERE `article_id` = 435798 LIMIT 1   [ RunTime:0.0004s ]
SHOW COLUMNS FROM `article_cate` [ RunTime:0.0015s ]
SELECT `name` FROM `article_cate` WHERE `id` = 259 LIMIT 1   [ RunTime:0.0001s ]
SELECT * FROM `article` WHERE `bloger_id` = 783 ORDER BY view_count desc LIMIT 0,10   [ RunTime:0.0003s ]
    
    
        
    
	    
    
    



0.0484s