今天面试一直聊得顺利,然后面试官就出了一道 SQL 题目让我当场写语句。题目是这样的:
有这样两个表: name 和 hobby
name 表有两个字段, ID 和 Name ,代表用户的 ID 和名字,ID 唯一
hobby 表有两个字段, ID 和 Hobby ,代表用户的 ID 和兴趣,每个用户都会有许多兴趣
如图所示:
现在要求列出兴趣数量大于等于 3 个的用户的 Name 。
我是这么写的:
1 2 3 4 5 6 7 8 9 |
SELECT `name`.`Name` FROM `name` INNER JOIN hobby ON `name`.ID = hobby.ID GROUP BY hobby.ID HAVING count(hobby.Hobby) >= 3 |
面试官认为这样写不对,而且应该用效率更高的写法。小白求大家指点一下,这道题大概有几种写法?哪种写法的效率是最高的。
======================================================
另一种写法,用 MySQL explain 后发现效率比上面那种还要低:
1 2 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 |
最新评论
要按照名字分组啊
赞
同求指教
赞
先查Hobby表 查出大于数量3的ID,再查Name
这样可以减少查询name表的rows
1 赞
你这种如果数据量大的话并不一定快,你这个相当于子查询了,走的索引也不是最快的
赞
你的意思是用 EXIST ?
赞
不对啊,我忘了hobby的id就是用户id了,这样写没问题啊
赞
赞
是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
这样就没有问题了
赞
这句不懂求解释。
赞
这个是个聚合函数啊,或者你换成其他聚合函数也可以的
赞
效果是一样的,也没看出在效率上有什么加快。
我想应该是有效率更高一点的写法吧?
赞
有大神提供了另外一种写法:
请教一下各位大神这种写法比我正文上写法的效率更高吗?
2 赞
这个应该就是最好的单条sql写法,,我想的是先筛选兴趣…再用in….分2条sql . 为啥分2条写楼上有解释…..子查询问题很大
赞
这种方法减少了临时表的大小,或者可以用exist或者not exists或许可以避免创建临时表。
赞
怎么用 EXIST ?
赞
ID上创建了索引,这个是最优的写法
1 赞
有道理,我没有考虑到索引对效率的影响。
赞
hobby表需要分组统计,默认全表扫描,所以可以的话先过滤部分数据,如hobby为空的
name表和hobby表的ID字段关联查询,因为name的 ID唯一,所以一般会走索引
实际效果主要看hobby表的数据分布情况
赞
赞
这两种写法都没有用连接,哪种效率较高呢?
赞
一般来说exists可能会in有效率
涉及表的时候,通常是exists
如果只是字符串,我觉得in是一个好东西
赞
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
赞
不知道这种效率如何
select `name` from `name` where `id` in (select `id` from `hobby` group by `id` having count(`hobby`) > 3)
赞
select Name from name where Name.Id in (select hobby.Id ,count(hobby.Id) as num from hobby where num > 2);
赞
忘记分组了
赞
你这是因为连接查询导致效率低吧
赞
确实,用 EXIST 的话,效率才会高。
赞
SELECT b.Name FROM hobby a
WHERE COUNT(a.Hobby)>=3 GROUP BY a.Hobby
LEFT JOIN NAME b ON a.id = b.id
赞
oracle:select name from name where id in(select id from hobby having count(hobby)>=3 group by id)
赞
为啥要强调 Oracle ,你这种跟楼上的 http://group.jobbole.com/30870/#comment-86768 不是一模一样吗?
赞
select name.name from name where id in (
select id from hobby group by id having count(hobby) >3 )
感觉这个性能还可以。再优化,就很细致了。。懒得想。
赞
感觉怎么写都行…没实体数据测试没人知道
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
赞
可能面试官认为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
赞