面试遇到的一条看上去简单的 SQL 题目，小白求大家指点

小组 › 技术

面试遇到的一条看上去简单的 SQL 题目，小白求大家指点

05/26• 一笑奈何~ •29 评论 • sql

今天面试一直聊得顺利，然后面试官就出了一道 SQL 题目让我当场写语句。题目是这样的：

有这样两个表： name 和 hobby

name 表有两个字段， ID 和 Name ，代表用户的 ID 和名字，ID 唯一

hobby 表有两个字段， ID 和 Hobby ，代表用户的 ID 和兴趣，每个用户都会有许多兴趣

如图所示：

现在要求列出兴趣数量大于等于 3 个的用户的 Name 。

我是这么写的：

SELECT
	`name`.`Name`
FROM
	`name`
INNER JOIN hobby ON `name`.ID = hobby.ID
GROUP BY
	hobby.ID
HAVING
	count(hobby.Hobby) >= 3

SELECT

`name`.`Name`

FROM

`name`

INNER JOIN hobby ON `name`.ID = hobby.ID

GROUP BY

hobby.ID

HAVING

count(hobby.Hobby) >= 3

面试官认为这样写不对，而且应该用效率更高的写法。小白求大家指点一下，这道题大概有几种写法？哪种写法的效率是最高的。

======================================================

另一种写法，用 MySQL explain 后发现效率比上面那种还要低：

SELECT Name 
FROM `name`, (SELECT id ,count(id) AS count1 from hobby group by id ) AS a 
WHERE `name`.id = a.id AND a.count1>=3

SELECT Name

FROM `name`, (SELECT id ,count(id) AS count1 from hobby group by id ) AS a

WHERE `name`.id = a.id AND a.count1>=3

1 赞 1 收藏

直接登录

最新评论

男人泪（ 1 · ）程序猿 05/26

要按照名字分组啊

赞回复

OSong 05/26

同求指教

赞回复

HengShuang （ 1 · ）程序猿 05/26

先查Hobby表查出大于数量3的ID，再查Name

这样可以减少查询name表的rows

1 赞回复

男人泪（ 1 · ）程序猿 05/26

你这种如果数据量大的话并不一定快，你这个相当于子查询了，走的索引也不是最快的

赞回复
一笑奈何~ （ 1 ） 05/26

你的意思是用 EXIST ？

赞回复

男人泪（ 1 · ）程序猿 05/26

不对啊，我忘了hobby的id就是用户id了，这样写没问题啊

赞回复

一笑奈何~ （ 1 ） 05/26

我也觉得没问题呀~~(>_<)~~

1

我也觉得没问题呀~~(>_<)~~

赞回复

男人泪（ 1 · ）程序猿 05/26

是group by的问题，带有group by的查询列要么是分组中的列要么是聚合函数,
SELECT
max(`name`.`Name`) name
FROM
`name`
INNER JOIN hobby ON `name`.ID = hobby.ID
GROUP BY
hobby.ID
HAVING
count(hobby.Hobby) >= 3
这样就没有问题了

赞回复

一笑奈何~ （ 1 ） 05/26

max(`name`.`Name`)

1

max(`name`.`Name`)

这句不懂求解释。

赞回复

男人泪（ 1 · ）程序猿 05/26

这个是个聚合函数啊，或者你换成其他聚合函数也可以的

赞回复

一笑奈何~ （ 1 ） 05/28

效果是一样的，也没看出在效率上有什么加快。

我想应该是有效率更高一点的写法吧？

赞回复

一笑奈何~ （ 1 ） 05/28

有大神提供了另外一种写法：

SELECT `name`.`Name` FROM ( SELECT ID FROM hobby GROUP BY id HAVING count(Hobby) >= 3 ) AS a INNER JOIN `name` ON `name`.ID = a.ID

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT
`name`.`Name`
FROM
(
SELECT
ID
FROM
hobby
GROUP BY
id
HAVING
count(Hobby) >= 3
) AS a
INNER JOIN `name` ON `name`.ID = a.ID

请教一下各位大神这种写法比我正文上写法的效率更高吗？

2 赞回复

(*__*) （ 1 ） 05/31

这个应该就是最好的单条sql写法,,我想的是先筛选兴趣…再用in….分2条sql . 为啥分2条写楼上有解释…..子查询问题很大

赞回复
朱昊（ 1 ） 05/31

这种方法减少了临时表的大小，或者可以用exist或者not exists或许可以避免创建临时表。

赞回复

一笑奈何~ （ 1 ） 06/01

怎么用 EXIST ？

赞回复

赶场网（ 1 ） 05/31

ID上创建了索引，这个是最优的写法

1 赞回复

一笑奈何~ （ 1 ） 06/01

有道理，我没有考虑到索引对效率的影响。

赞回复

nicoke （ 1 ） 06/02

-- 写法一
select name from name where exists(select count(hobby.id) from hobby where hobby.id=name.id group by id having count(id)>=3);

-- 写法二
select name from name where id in(select id from hobby where hobby.id=name.id group by id having count(id)>=3);

-- 写法一

select name from name where exists(select count(hobby.id) from hobby where hobby.id=name.id group by id having count(id)>=3);

-- 写法二

select name from name where id in(select id from hobby where hobby.id=name.id group by id having count(id)>=3);

一笑奈何~ （ 1 ） 06/06

这两种写法都没有用连接，哪种效率较高呢？

赞回复

qzm （ 1 ） 06/03

select n.id,n.name,h.hobby from name n, hobby h where n.id = h.id group by id having count(h.hobby) > 3

赞回复
小枫Jeffrey 06/03

不知道这种效率如何

select `name` from `name` where `id` in (select `id` from `hobby` group by `id` having count(`hobby`) > 3)

赞回复
阿九（ 1 · ） php 06/04

select Name from name where Name.Id in (select hobby.Id ,count(hobby.Id) as num from hobby where num > 2);

赞回复

阿九（ 1 · ） php 06/04

忘记分组了

赞回复

范特西111 06/05

你这是因为连接查询导致效率低吧

赞回复

一笑奈何~ （ 1 ） 06/06

确实，用 EXIST 的话，效率才会高。

赞回复

乌托邦（） java开发 06/07

SELECT b.Name FROM hobby a
WHERE COUNT(a.Hobby)>=3 GROUP BY a.Hobby
LEFT JOIN NAME b ON a.id = b.id

赞回复
Tjagerva （ 1 ） 06/07

oracle：select name from name where id in(select id from hobby having count(hobby)>=3 group by id)

赞回复

一笑奈何~ （ 1 ） 06/08

为啥要强调 Oracle ，你这种跟楼上的 http://group.jobbole.com/30870/#comment-86768 不是一模一样吗？

赞回复

bluemoon1999 （ 1 ）测试工程师 07/25

select name.name from name where id in (
select id from hobby group by id having count(hobby) >3 )

感觉这个性能还可以。再优化，就很细致了。。懒得想。

赞回复

相关话题

相关文章

最新评论