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

yuanyu5237

浏览: 162792 次
性别:
来自: 北京

最近访客更多访客>>

wwg_willy

qq85609655

supportopensource

几人憔悴几人泪

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

database & sql

SQL 面试游戏 Oracle F#

有一张表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最大游戏数的玩家名。

分享到：

Ubunu下搭建android NDK环境 | ubuntu11.04下搭建php环境

2011-05-05 10:42
浏览 3723
评论(70)
论坛回复 / 浏览 (79 / 55194)
分类:数据库
查看更多

70 楼布鲁斯 2011-05-26

Select * From (Select * From t_stu s Where s.gender = 'f'Order By grade Desc) Where Rownum <=5
Union
Select * From (Select * From t_stu s Where s.gender = 'm'Order By grade Desc) Where Rownum <=5;

69 楼 guizhou851213 2011-05-25

select t3.* from (
                    select t.* from (select name,grade,gender from t_stu t where gender = 0 order by grade desc limit 0, 5) t
                    union all
                    select t2.* from (select name,grade,gender from t_stu t where gender = 1 order by grade desc limit 0, 5) t2
                    ) t3 order by t3.gender,t3.grade desc
试试这个。

68 楼 cxw03403 2011-05-24

db2数据库：t_stu；其中三个字段:name,gender,grade
select * from ( select info2.* from t_stu info2 where gender='1' order by grade asc fetch first 5 rows only ) as tb
union
select * from (select info1.* from t_stu info1 where gender='2' order by grade asc fetch first 5 rows only ) as tb1

67 楼 advantech 2011-05-24

kenny_no2 写道

没必要分性别啊，除了男不就女么？难道还有人要？所以我感觉性别是误导你的。

人才啊，运动会也别分男女了，大家一起比吧。

66 楼 xzuse 2011-05-23

kenny_no2 写道

没必要分性别啊，除了男不就女么？难道还有人要？所以我感觉性别是误导你的。

人才！

65 楼 pengshan 2011-05-22

这个题的本意应该是要用UNION合并两个查询，得到的效率也是最高的。如果不用UNION，我给了一个例子，在SQL SERVER中测试成功过的。

declare @t_stu table(
	id int primary key identity(1,1),
	name varchar(100),
	sex int,
	grade int
)

insert into @t_stu(name, sex, grade) values('xiaoming01', 0, 100)
insert into @t_stu(name, sex, grade) values('xiaoming2', 1, 99)
insert into @t_stu(name, sex, grade) values('xiaoming03', 0, 90)
insert into @t_stu(name, sex, grade) values('xiaoming04', 0, 80)
insert into @t_stu(name, sex, grade) values('xiaoming5', 1, 10)
insert into @t_stu(name, sex, grade) values('xiaoming6', 1, 100)
insert into @t_stu(name, sex, grade) values('xiaoming07', 0, 70)
insert into @t_stu(name, sex, grade) values('xiaoming8', 1, 50)
insert into @t_stu(name, sex, grade) values('xiaoming011', 0, 100)
insert into @t_stu(name, sex, grade) values('xiaoming12', 1, 99)
insert into @t_stu(name, sex, grade) values('xiaoming013', 0, 90)
insert into @t_stu(name, sex, grade) values('xiaoming014', 0, 80)
insert into @t_stu(name, sex, grade) values('xiaoming15', 1, 10)
insert into @t_stu(name, sex, grade) values('xiaoming16', 1, 100)
insert into @t_stu(name, sex, grade) values('xiaoming017', 0, 70)
insert into @t_stu(name, sex, grade) values('xiaoming18', 1, 50)

select * from @t_stu a where a.id in (select top 5 id from @t_stu b where a.sex = b.sex order by grade DESC)
	order by a.sex ASC, a.grade DESC

得到的结果如下：
id   name    sex         grade
------------------------------------
1    xiaoming01     0    100
9    xiaoming011    0    100
3    xiaoming03     0    90
11   xiaoming013    0    90
4    xiaoming04     0    80
6    xiaoming6      1    100
14   xiaoming16     1    100
10   xiaoming12     1    99
2    xiaoming2      1    99
8    xiaoming8      1    50

64 楼 kenny_no2 2011-05-20

没必要分性别啊，除了男不就女么？难道还有人要？所以我感觉性别是误导你的。

63 楼 yutuer 2011-05-19

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)
这个不是想要的结果吗?

我也发现了...只要加上括号就可以了

62 楼 bobocici 2011-05-18

这个不就是很简单的分析函数么？
select * from (
select name,grade,gender ,rank() over (partition by gender order by grade desc nulls last) rnk from t_stu
) where rnk<=5

61 楼 wf1006 2011-05-18

SELECT ROWNUM, V.*
   FROM (select * from t_stu t1 where t1.gender = 'M' order by t1.grade asc) V
WHERE ROWNUM < 6
UNION ALL
SELECT ROWNUM, V.*
   FROM (select * from t_stu t1 where t1.gender = 'F' order by t1.grade asc) V
WHERE ROWNUM < 6

-- Create table
create table T_STU
(
ID     NUMBER,
NAME   VARCHAR2(50),
GENDER VARCHAR2(1),
GRADE NUMBER
)
tablespace PIP_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

60 楼 sbkyv 2011-05-17

MySQL

SELECT a.* FROM `t_stu` a INNER JOIN ((SELECT gender,grade FROM `t_stu` WHERE gender=0 group BY grade DESC LIMIT 4,1) UNION ALL (SELECT gender,grade FROM `t_stu` WHERE gender=1 group BY grade DESC LIMIT 4,1)) b ON a.grade>=b.grade AND a.gender=b.gender ORDER BY gender,grade DESC

这样不行，当分数出现少于五个的情况

59 楼 jackcfan 2011-05-17

grantliping 写道

SELECT * FROM(SELECT * FROM T_STU WHERE GENDER=1 ORDER BY GRADE DESC) where rownum<=5
UNION
SELECT * FROM (SELECT * FROM T_STU WHERE GENDER=0 ORDER BY GRADE DESC )where rownum<=5;

正解

莫非也是第一次发帖。。。。

58 楼 grantliping 2011-05-17

SELECT * FROM(SELECT * FROM T_STU WHERE GENDER=1 ORDER BY GRADE DESC) where rownum<=5
UNION
SELECT * FROM (SELECT * FROM T_STU WHERE GENDER=0 ORDER BY GRADE DESC )where rownum<=5;

正解

57 楼 jackcfan 2011-05-17

select * from t_stu a where 5>(select count(*) FROM t_stu where gender=a.gender AND grade>a.grade) order by a.grade desc

刚看到这个sql时还觉得很新颖思路很特别

我新建了一个张表并插入了 26万多行数据

结果用 union all 一秒不到就出结果

但上面的那个sql 过了十五分钟还没出结果果断中断执行。。。

56 楼 sebatinsky 2011-05-17

..好复杂，从来没有尝试过，看来跟不上这里步伐

55 楼 case0079 2011-05-16

引用

select * from t_stu a where 5>(select count(*) FROM t_stu where gender=a.gender AND grade>a.grade) order by a.grade desc

这个效率差的.
还是用union all好

54 楼 p2world 2011-05-15

<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>(select count(*) FROM t_stu where gender=a.gender AND grade>a.grade) order by a.grade desc</pre>
</div>
<p>++</p>
<p>高手</p>

53 楼 jetseter 2011-05-14

我认为结果应该小于等于 5行
最后显示的结果应该是这样的：
    姓名性别成绩姓名性别成绩
1 女一女   99   男一男   98
2 女二女   98   男二男   97
3 女三女   97   男三男   96
4 女四女   96   男四男   95
5                  男五男   94

这样才能给取结果时比较方便

而这种显示方式
    姓名性别成绩
1 女一女   99
2 女二女   98
3 女三女   97
4 女四女   96
5 男一男   98
6 男二男   97
7 男三男   96
8 男四男   95
9 男五男   94

在取值的时候会出现问题，后续要增加程序的判断（尤其是当其中比如女生根本不足五人的时候）
我觉得这条SQL题目应该考的就是这方面的考虑。之前有附上 sql。不知是否想多了。

52 楼 sbkyv 2011-05-13

MYSQL,之前已经发表一个查询，不过没人回应，在来一个

SELECT * FROM `t_stu` WHERE grade>=IFNULL((SELECT grade FROM `t_stu` WHERE gender=0 order BY grade DESC LIMIT 4,1),0) AND gender=0
UNION ALL
SELECT * FROM `t_stu` WHERE grade>=IFNULL((SELECT grade FROM `t_stu` WHERE gender=1 order BY grade DESC LIMIT 4,1),0) AND gender=1

---------------------------------------------------------------------
简化
SELECT * FROM `t_stu` a WHERE grade>=IFNULL((SELECT grade FROM `t_stu` WHERE gender=a.gender order BY grade DESC LIMIT 4,1),0)

不过还是觉得下面的好些
---------------------------------------------------------
附上之前的SQl

SELECT a.* FROM t_stu a INNER JOIN ((SELECT GROUP_CONCAT(Id) AS Id FROM t_stu where gender=1 GROUP BY grade DESC LIMIT 5) union all (SELECT GROUP_CONCAT(Id) AS Id FROM t_stu where gender=0 GROUP BY grade DESC LIMIT 5)) b ON FIND_IN_SET(a.id,b.Id)>0 ORDER BY grade DESC

51 楼 babaoqi 2011-05-13

一、先找出每种gender的第N个的grade的值minGrade
二、再查询相同gender中grade>= minGrade的记录

SELECT t.`name`, t.`gender`, t.`grade` FROM `t_stu` AS t,
(SELECT u2.gender,MIN(u2.grade) AS minValue FROM
(SELECT u1.`gender`, u1.`grade` FROM `t_stu` AS u1 WHERE u1.gender=1 ORDER BY u1.grade DESC LIMIT 5) AS u2
GROUP BY u2.gender
) AS tb1,
(SELECT u2.gender,MIN(u2.grade) AS minValue FROM
(SELECT u1.`gender`, u1.`grade` FROM `t_stu` AS u1 WHERE u1.gender=0 ORDER BY u1.grade DESC LIMIT 5) AS u2
GROUP BY u2.gender
) AS tb2
WHERE (t.gender=tb1.gender AND t.grade>=tb1.minValue) OR (t.gender=tb2.gender AND t.grade>=tb2.minValue)
ORDER BY t.gender,t.grade DESC

发表评论

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

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论