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

小组 › 技术

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

2017/05/26• 一笑奈何~ •33 评论 • 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 · ）程序猿 2017/05/26

要按照名字分组啊

赞回复

OSong 2017/05/26

同求指教

赞回复

HengShuang （ 1 · ）程序猿 2017/05/26

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

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

1 赞回复

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

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

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

你的意思是用 EXIST ？

赞回复

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

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

赞回复

一笑奈何~ （ 1 ） 2017/05/26

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

1

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

赞回复

男人泪（ 1 · ）程序猿 2017/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 ） 2017/05/26

max(`name`.`Name`)

1

max(`name`.`Name`)

这句不懂求解释。

赞回复

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

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

赞回复

一笑奈何~ （ 1 ） 2017/05/28

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

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

赞回复

一笑奈何~ （ 1 ） 2017/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 ） 2017/05/31

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

赞回复
朱昊（ 1 ） 2017/05/31

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

赞回复

一笑奈何~ （ 1 ） 2017/06/01

怎么用 EXIST ？

赞回复

赶场网（ 1 ） 2017/05/31

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

1 赞回复

一笑奈何~ （ 1 ） 2017/06/01

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

赞回复

eda_li 2017/12/03

hobby表需要分组统计，默认全表扫描，所以可以的话先过滤部分数据，如hobby为空的
name表和hobby表的ID字段关联查询，因为name的 ID唯一，所以一般会走索引
实际效果主要看hobby表的数据分布情况

赞回复

nicoke （ 1 ） 2017/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 ） 2017/06/06

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

赞回复

彼岸X （ 1 ） 2017/10/20

一般来说exists可能会in有效率

涉及表的时候，通常是exists

如果只是字符串，我觉得in是一个好东西

赞回复

qzm （ 1 ） 2017/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 2017/06/03

不知道这种效率如何

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

赞回复
阿九（ 1 · ） php 2017/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 2017/06/04

忘记分组了

赞回复

范特西111 2017/06/05

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

赞回复

一笑奈何~ （ 1 ） 2017/06/06

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

赞回复

乌托邦（） java开发 2017/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 ） 2017/06/07

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

赞回复

一笑奈何~ （ 1 ） 2017/06/08

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

赞回复

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

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

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

赞回复
彼岸X （ 1 ） 2017/10/20

感觉怎么写都行…没实体数据测试没人知道

SELECT name FROM

name ,(

SELECT id , COUNT(hobby) FROM hobby

GROUP BY id,hobby

HAVING COUNT(hobby) > 3) AS a

WHERE name.id = a.id

赞回复
千里走单骑软件攻城狮 2017/11/18

可能面试官认为HAVING效率比较低，换一种写法试试
SELECT Name FROM
(SELECT
MAX(`name`.Name) AS Name ,COUNT(`hobby`.Hobby) AS CNT
FROM
`name`
INNER JOIN `hobby` ON `name`.ID = `hobby`.ID) AS T
WHERE CNT>=3

赞回复

相关话题

相关文章

最新评论