`
yuanyu5237
  • 浏览: 162889 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

一道淘宝的考察sql语句的面试题

SQL面试游戏OracleF# 
阅读更多
有一张表t_stu；其中三个字段:name,gender,grade；要求用一条sql语句查出男生前5名和女生前五名。

小弟当时没做出来，只用最笨的办法解决： 
select name,grade from t_stu m where gender = 1 order by grade limit 1,2 union select name,grade from t_stu f where gender = 0 order by grade limit 1,2;

但回来后，发现这个也是错的，说union和order by错误用法，想了很久，终于有一个答案： 
select f.name,f.gender,f.grade,m.name,m.gender,m.grade,(f.grade+m.grade) gg from t_stu m, t_stu f where m.gender != f.gender m.name != f.name group by gg order by gg limit 1,3;

运行这个之后，感觉很不爽。希望看到这篇文章的朋友能给我找到一个好的答案。
还有，如果用oracle数据库怎么实现，还没试过，下面是我在mysql中的建表语句： 
CREATE TABLE `t_stu` (
  `id` int(4) NOT NULL DEFAULT '0',
  `name` varchar(16) DEFAULT NULL,
  `gender` int(2) DEFAULT NULL,
  `grade` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
)DEFAULT CHARSET=utf8 |

插入测试数据： 
insert into t_stu values(1,"Alex",1,91);
insert into t_stu values(2,"Elena",0,92);


附另外一道在别处碰到的sql题：
有一张表t_game，记录了游戏玩家的id，游戏玩家名name，和每个玩家玩的游戏game，请找出玩游戏最多的那个玩家，我用的子查询，先count每个游戏玩家玩的游戏数，然后max最大游戏数的玩家名。

分享到：
| ubuntu11.04下搭建php环境
评论
30 楼 java.com.cn 2011-05-09  
mysql:
select * from user order by gender, grade desc limit 10
29 楼 hzl7652 2011-05-09  
<p>mysql</p>
<p>
</p>
<pre name="code" class="sql">SELECT a.* FROM (SELECT NAME FROM t_stu WHERE gender = 1 ORDER BY grade LIMIT 2)a
UNION ALL
SELECT a.* FROM (SELECT NAME FROM t_stu WHERE gender = 0 ORDER BY grade LIMIT 2)a</pre>

<p> oracle </p>
<p>
</p>
<pre name="code" class="sql">select a.* from (
select name,grade, row_number() over(partition by gender order by grade) as rn from t_stu
) a where a.rn  &lt; 6</pre>
 
28 楼 xuehanxin 2011-05-09  
呵呵，都是人才啊
27 楼 canshou 2011-05-09  
小小风信子 写道
如果是使用oracle的话，应该是考查分析函数和开窗函数的使用，最近一直在写这个，现在给出oracle的写法： 
CREATE TABLE T_STU(  
       ID NUMBER(38) PRIMARY KEY, 
       NAME VARCHAR2(20),  
       GENDER NUMBER(1),  
       GRADE NUMBER(4)  
); 

insert into t_stu values(1,'Alex',1,91);     
insert into t_stu values(2,'Elena',0,92);    
insert into t_stu values(3,'Alex2',1,92);     
insert into t_stu values(4,'Elena2',1,92);    
insert into t_stu values(5,'Alex3',1,98);     
insert into t_stu values(6,'Elena3',1,98);    
insert into t_stu values(7,'Alex4',1,98);     
insert into t_stu values(8,'Elena4',1,92);    
insert into t_stu values(9,'Alex5',1,51);     
insert into t_stu values(10,'Elena5',0,90);    
insert into t_stu values(11,'Alex6',1,90);     
insert into t_stu values(12,'Elena6',0,90);   
insert into t_stu values(13,'Elena7',0,89);  
insert into t_stu values(14,'Elena8',0,87); 
insert into t_stu values(15,'Alex5',1,60); 
insert into t_stu values(16,'Alex5',1,70); 


SELECT * 
  FROM (SELECT s.*,
               DENSE_RANK() OVER(PARTITION BY s.gender ORDER  BY s.grade DESC) dro 
	       FROM t_stu s ) t
 WHERE t.dro <= 5

下面的图片是执行sql后的效果，当然最后一列是不需要的，这里显示只是为了让你明白
[img]

[/img]




这个不错，看样子自己需要学习了。。
26 楼 fflame 2011-05-09  
oracle下的结果，执行是成功了，但是很土：
select * from (select * from S_FIELD where table_name='ST_DLTB' order by LIST_ORDER desc) b where rownum<6 union select * from (select * from S_FIELD where table_name='ST_DLTB_BG' order by LIST_ORDER desc) c where rownum<6
25 楼 hackpro 2011-05-09  
select * from  s_stu
where name in (
select name from s_stu where gender='男'　and rownum<6  order grade
)
or
name in (
select name from s_stu where gender='女'　and rownum<6  order grade
)
24 楼 jackra 2011-05-09  
select * from t_stu
where
(gender='0'
and grade >=select min(grade) from t_stu where gender='0' and rownum<=5 order by grade desc)
or(gender='1'
and grade >=select min(grade) from t_stu where gender='1' and rownum<=5
order by grade desc)
order by gender,grade desc

时间久了不用数据库，有点手生。。。。。

用子查询查找前5的最低分数，然后把所有大于这个分数的数据列出来就行了。感觉应该还行吧。少于5人的情况也能处理。。。。。。
23 楼 weaponhuang 2011-05-08  
--mysql
select * from t_stu where ID in (select ID from t_stu where gender='0' an
d order by grade desc limit 5) or ID in (select ID from t_stu where gender='1' a
nd grade >90 order by grade desc limit 5) order by gender;
这样？？
22 楼 zhdkn 2011-05-08  
select name,gender,grade from (
select  name ,
gender  ,
grade ,row_number() over(partition by gender order by grade desc )  n
from t_stu ) where n<=5 ;

row_number() over(partition by gender order by grade desc )在oracle下可用表示按照 gender分组，然后没组里面按照grade排序
21 楼 renjie120 2011-05-08  
xiangzi21 写道
(select * from t_stu where gender = 0 order by grade desc limit 5)
union all
(select * from t_stu where gender = 1 order by grade desc limit 5)
这个不是想要的结果吗?

这个就可以吧？我也是这么想的。lz有什么意见么？这样写的话？
20 楼 tfwin2 2011-05-07  
<div class="quote_title">yuehan 写道</div>
<div class="quote_div">
<div class="quote_title">SilenceGG 写道</div>
<div class="quote_div">
<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>+1</p>
</div>
<p><br>此写法性能极差！</p>
<p>首先全表扫描，每次扫描都会嵌套count统计.</p>
</div>
<p><br>呵呵，虽然性能也不高，但是他这个比UNION ALL 要强，看了下执行计划，只扫一次表，UNION ALL的写法，比这个要扫更多次。</p>
19 楼 jiangkefei 2011-05-07  
SELECT TOP 5 * FROM T_STU T1,T_STU T2 WHERE T1.GENDER=1 AND T2.GENDER=0 ORDER BY T1.GRADE DESC,T2.GRADE DESC;
mssql的语法，
查出的结果里，T1为男生前五名的，T2为女生前五名的。
18 楼 yuanyu5237 2011-05-07  
谢谢各位老大的回复，我只学过一学期oracle，后来就没怎么用过，知道rownow可以查前几名，但是开窗函数不知道，我好好学一下，sqlserver里有top，mysql里有limit都可求前几名，题目是要求把男生中的分数前五和女生中的分数前五查出来，如果用两条语句就很简单，我想难的是如何只用一条语句，再次感谢各位前辈的回复，我先试一下mysql和sqlserver的答案，目前oracle刚刚装好，要先学一下开窗函数。还有不知道为什么我答案里那个union的关键字报错。
17 楼 yuehan 2011-05-07  
<div class="quote_title">SilenceGG 写道</div>
<div class="quote_div">
<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>+1</p>
</div>
<p><br>此写法性能极差！</p>
<p>首先全表扫描，每次扫描都会嵌套count统计.</p>
16 楼 czwlucky 2011-05-07  
小小风信子 写道
如果是使用oracle的话，应该是考查分析函数和开窗函数的使用，最近一直在写这个，现在给出oracle的写法： 
CREATE TABLE T_STU(  
       ID NUMBER(38) PRIMARY KEY, 
       NAME VARCHAR2(20),  
       GENDER NUMBER(1),  
       GRADE NUMBER(4)  
); 

insert into t_stu values(1,'Alex',1,91);     
insert into t_stu values(2,'Elena',0,92);    
insert into t_stu values(3,'Alex2',1,92);     
insert into t_stu values(4,'Elena2',1,92);    
insert into t_stu values(5,'Alex3',1,98);     
insert into t_stu values(6,'Elena3',1,98);    
insert into t_stu values(7,'Alex4',1,98);     
insert into t_stu values(8,'Elena4',1,92);    
insert into t_stu values(9,'Alex5',1,51);     
insert into t_stu values(10,'Elena5',0,90);    
insert into t_stu values(11,'Alex6',1,90);     
insert into t_stu values(12,'Elena6',0,90);   
insert into t_stu values(13,'Elena7',0,89);  
insert into t_stu values(14,'Elena8',0,87); 
insert into t_stu values(15,'Alex5',1,60); 
insert into t_stu values(16,'Alex5',1,70); 


SELECT * 
  FROM (SELECT s.*,
               DENSE_RANK() OVER(PARTITION BY s.gender ORDER  BY s.grade DESC) dro 
	       FROM t_stu s ) t
 WHERE t.dro <= 5

下面的图片是执行sql后的效果，当然最后一列是不需要的，这里显示只是为了让你明白
[img]

[/img]


不知道前五名是如何定义的，可以参考三个分析函数进行查询，具体使用哪一个，就看是如何定义这个“前五名”的。
row_number() 按序分配序列,如: 1, 2 ,3 ,4 ,5
rank() 并列排名会占取下一个名次，如： 1, 2, 2, 4, 5
dense_rank() 并列名次不会占取下一名次,如：1, 2, 2, 3, 4, 5
15 楼 tfwin2 2011-05-07  
DENSE_RANK() 跟RANK还是有不同的，不知道楼主的题目是指哪一种了，明显DENSE_RANK() 要多很多人，呵呵，不过他的名次是不跳级的，RANK()会跳过相同排名的次数。
14 楼 sniffer123 2011-05-07  
小小风信子 写道
如果是使用oracle的话，应该是考查分析函数和开窗函数的使用，最近一直在写这个，现在给出oracle的写法： 
CREATE TABLE T_STU(  
       ID NUMBER(38) PRIMARY KEY, 
       NAME VARCHAR2(20),  
       GENDER NUMBER(1),  
       GRADE NUMBER(4)  
); 

insert into t_stu values(1,'Alex',1,91);     
insert into t_stu values(2,'Elena',0,92);    
insert into t_stu values(3,'Alex2',1,92);     
insert into t_stu values(4,'Elena2',1,92);    
insert into t_stu values(5,'Alex3',1,98);     
insert into t_stu values(6,'Elena3',1,98);    
insert into t_stu values(7,'Alex4',1,98);     
insert into t_stu values(8,'Elena4',1,92);    
insert into t_stu values(9,'Alex5',1,51);     
insert into t_stu values(10,'Elena5',0,90);    
insert into t_stu values(11,'Alex6',1,90);     
insert into t_stu values(12,'Elena6',0,90);   
insert into t_stu values(13,'Elena7',0,89);  
insert into t_stu values(14,'Elena8',0,87); 
insert into t_stu values(15,'Alex5',1,60); 
insert into t_stu values(16,'Alex5',1,70); 


SELECT * 
  FROM (SELECT s.*,
               DENSE_RANK() OVER(PARTITION BY s.gender ORDER  BY s.grade DESC) dro 
	       FROM t_stu s ) t
 WHERE t.dro <= 5

下面的图片是执行sql后的效果，当然最后一列是不需要的，这里显示只是为了让你明白
[img]

[/img]

太久没用oracle 都忘了有窗口查询了
13 楼 yhz61010 2011-05-07  
select t_stu.name, t_stu.grade, t_stu.gender
from t_stu
where gender = 1 and grade in
(select distinct(grade) as mark from t_stu where gender = 1 order by grade desc limit 5)
or gender = 0 and grade in
(select distinct(grade) as mark from t_stu where gender = 0 order by grade desc limit 5)
order by gender, grade desc

楼主可以验证下，看看上面这条SQL写的对不对。
12 楼 小小风信子 2011-05-07  
如果是使用oracle的话，应该是考查分析函数和开窗函数的使用，最近一直在写这个，现在给出oracle的写法： 
CREATE TABLE T_STU(  
       ID NUMBER(38) PRIMARY KEY, 
       NAME VARCHAR2(20),  
       GENDER NUMBER(1),  
       GRADE NUMBER(4)  
); 

insert into t_stu values(1,'Alex',1,91);     
insert into t_stu values(2,'Elena',0,92);    
insert into t_stu values(3,'Alex2',1,92);     
insert into t_stu values(4,'Elena2',1,92);    
insert into t_stu values(5,'Alex3',1,98);     
insert into t_stu values(6,'Elena3',1,98);    
insert into t_stu values(7,'Alex4',1,98);     
insert into t_stu values(8,'Elena4',1,92);    
insert into t_stu values(9,'Alex5',1,51);     
insert into t_stu values(10,'Elena5',0,90);    
insert into t_stu values(11,'Alex6',1,90);     
insert into t_stu values(12,'Elena6',0,90);   
insert into t_stu values(13,'Elena7',0,89);  
insert into t_stu values(14,'Elena8',0,87); 
insert into t_stu values(15,'Alex5',1,60); 
insert into t_stu values(16,'Alex5',1,70); 


SELECT * 
  FROM (SELECT s.*,
               DENSE_RANK() OVER(PARTITION BY s.gender ORDER  BY s.grade DESC) dro 
	       FROM t_stu s ) t
 WHERE t.dro <= 5

下面的图片是执行sql后的效果，当然最后一列是不需要的，这里显示只是为了让你明白
[img]

[/img]
11 楼 tfwin2 2011-05-07  
想不出来，放弃了，关键是第四，第五，第六的成绩一样的话，取第5名的成绩做为判断条件没有意义，
如果非要一条SQL的话，我只想到用分析函数RANK应该可以解决，坐等答案了。 

--rank()
SELECT * FROM(SELECT RANK() OVER(PARTITION BY GENDER ORDER BY GRADE DESC) RANK,A.* FROM  T_STU A )WHERE RANK<=5;
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    sql语句的内联外联 经典面试题

    在面试中，SQL语句的内联外联经典面试题是一个非常重要的考察点。本文将对SQL语句的内联外联进行详细的介绍和分析，并提供了一些经典的面试题和答案，以便读者更好地理解和掌握SQL语句的内联外联。 一、SQL语句的...

    java私塾面试题----SQL语句2

    在Java私塾面试中，SQL语句是必不可少的考察点，因为SQL是数据操作和管理的核心语言，尤其对于Java开发者来说，理解并熟练运用SQL至关重要。以下是对SQL语句的一些关键知识点的详细解释： 1. **SQL基础概念**：SQL...

    oracle 数据库sql 语句面试题，北京一家软件公司的面试题，难度中等

    ### Oracle数据库SQL语句面试题解析 #### 题目一：两个表的连接与比较 **题目背景：** 给出两个表`T1`和`T2`，每个表都只包含一个字段`ID`（整数类型）。`T1`中有4条记录：1，2，3，4；`T2`中有3条记录：2，4，5。...

    SQL经典面试题及答案 CASE

    《SQL经典面试题及答案解析》 SQL是Structured Query Language的缩写，是用于管理和处理关系数据库的标准语言。在面试中，SQL问题通常涉及到数据查询、聚合、分组、条件判断等多个方面，考察应聘者对数据库操作的...

    sql server面试题

    以下是一些可能的SQL Server面试题及详细解答： 1. **SQL基本操作** - **查询语句**：询问如何使用SELECT语句进行复杂查询，如JOIN、子查询、聚合函数（COUNT, SUM, AVG, MAX, MIN）。 - **DML操作**：如何插入...

    SQL经典面试题及答案

    这些面试题展示了SQL在数据分析和查询中的灵活性和实用性。理解并熟练掌握`SELECT`, `GROUP BY`, `CASE WHEN`, `JOIN`, `DATEDIFF`等核心概念对于任何IT专业人员，尤其是数据分析师或数据库管理员来说都是至关重要的...

    数据分析面试题-SQL面试题汇总.docx

    数据分析面试题-SQL面试题汇总 本文档汇总了两道数据分析面试题，涵盖了 SQL 编程语言的多个方面，包括建表、插入数据、排序、连接、分组、聚合函数、日期操作等。通过这两道题目，我们可以了解数据分析师需要具备...

    SQL经典面试题-很有用

    以下是基于给定的面试题及其解决方案的详细解析： 1. **分组计数问题**： 这个问题旨在考察对`GROUP BY`、`CASE`语句以及聚合函数的理解。题目要求统计每天胜利和失败的次数。有三种不同的解决方案： - 第一种...

    SQL面试题2

    尤其在面试中，数据库相关问题常常是考察候选人技术水平和经验的重要部分。以下将对提供的文件内容中提及的SQL知识点进行详细说明。 首先，文件描述中提到了几个基础的SQL命令，分别是DROP、TRUNCATE和DELETE，它们...

    sql考试面试题大全（真的很全）

    在SQL面试中，考察点通常包括SQL语句的编写、数据库操作、存储...以上就是对SQL面试题的详细解答，涵盖了SQL语句编写、聚合函数、存储过程、函数以及复杂查询等多个方面，旨在帮助理解和掌握SQL在实际问题中的应用。

    SQL面试题目-sqlServer

    解释：这道题目考察了候选人对 SQL 语句的掌握程度，特别是使用 NOT IN 语句来排除某些条件。这里我们使用了两条SELECT语句，第一条SELECT语句查询出所有大于等于 80 分的学生姓名，然后使用NOT IN语句排除这些学生...

    校招sql 面试题

    ### SQL面试题解析 #### 背景介绍 在IT行业的招聘过程中，SQL技术作为数据处理的核心工具之一，经常被用作考核应聘者能力的重要环节。对于即将步入职场的应届毕业生来说，掌握一定的SQL技能是必不可少的。下面将对...

    net sql java 面试技巧 个公司的面试题 简历模板

    在IT行业中，面试是检验求职者技能和...通过深入了解.NET、SQL和Java的核心概念，熟悉常见的面试题，以及准备一份详实的简历，你将在面试中展现出自己的专业能力和潜力。不断学习和实践，将是IT行业持续发展的关键。

    Sql面试题大全

    ### SQL面试题解析 #### 一、数据库部分 ##### 1. 根据部门号从高到低，工资从低到高列出每个员工的信息 **题目解析：** 此题考查了SQL中的`ORDER BY`子句，特别是多字段排序的能力。`ORDER BY`允许我们按照一个...

    数据库 sql 面试题大全

    10. **性能优化**：了解如何分析查询性能，使用EXPLAIN分析查询执行计划，以及调整SQL语句和数据库配置以提升性能。 11. **数据库安全**：包括用户权限管理、角色、权限分配以及如何保护数据库免受恶意攻击。 12. ...

    sql 面试题.rar

    在SQL（Structured Query Language）面试中，面试官通常会考察候选人的数据操作、查询优化、数据库设计、事务处理、并发控制、存储引擎以及安全性等方面的知识。以下是对这些关键知识点的详细解析： 1. **数据操作*...

    SQL常见面试题-程序员面试总结出来的宝贵经验

    SQL常见面试题 SQL 是 Structured Query Language 的缩写，作为一种标准的数据库语言，广泛应用于关系数据库管理系统中。在面试中， SQL 问题是必考题之一，本文总结了常见的 SQL 面试题，旨在帮助即将找工作的同学...

    sql数据库常见面试题

    数据库面试中，面试官经常通过一些常见面试题来考察应聘者对SQL语言的熟练程度，理解程度以及实际应用能力。本文将介绍一些SQL数据库的常见面试题，它们不仅覆盖了基础知识点，也包含了一些实际操作中的应用技巧。 ...

    sql面试题,JAVA常见面试题120道

    10. **面试技巧**：除了技术知识，面试官还会考察解决问题的能力、团队协作、沟通技巧等软技能。 对于SQL： 1. **基本查询**：掌握SELECT语句，包括WHERE子句、JOIN操作、子查询、聚合函数（COUNT, SUM, AVG, MAX,...

    程序员sql面试题

    以上SQL面试题不仅涵盖了基础的查询语句，还涉及到了复杂的子查询、数据关联、聚合函数等高级特性，是检验程序员SQL技能的有效方式。理解和熟练掌握这些知识点，对于提升数据库操作能力、优化查询效率具有重要意义。

Magicbox
Global site tag (gtag.js) - Google Analytics