某个大公司的sql面试题，自己不太会做有没有童鞋指点一下

自己对sql的理解。用一句俗话说：人有七窍，已经通了六窍，有没有童鞋指点一下

问：关系模式：User（userId, userName）， Article（articleId, userId, title,   content），Vote（articleId, score），User为用户关系，Article为用户发表的文章关系，Vote为文章得票关系，title为文章标题、score为得票数。
（1）用SQL语言查询所有没发表过文章的用户名；
（2）用SQL语言查询得票数大于100的所有文章标题，按得票数倒序排列；
（3）用SQL语言查询出发表文章数大于5，文章平均得票数大于100的用户名，按平均得票数倒序排列；
（4）设计这些表的主键、外键和索引，并指出上面三个查询所使用的索引。
（5）当用户数超过1000万，文章数超过1亿时，如何考虑存储及性能的改进和优化？

 

答： 

 

1 select * from User where useid  not in(select userid from Article);
2 select title from article   inner  join vote  on article.articleid=vote.aritcleid and  vote.score>100 order by vote.score asc;
3有点不太会，下面胡乱乱写了一通

3 select * from user where userid in(select userid from Article inner join vote on article.articleid = vote.articleid group by userId  having avg(score)>100) group by userid having count(*) >5;

4主键外键应该很简单，索引第一个应该是userid,第二个是articleid 和score，第三个应该是articleid和 userid

5用户数按照id分割分布式存储，文章类似，还可以用读写分离等策略水平扩展数据库.

评论

26 楼 Mrpublic 2010-08-18  

lerous 写道

kakaluyi 写道

谢谢，第3个题目看来只要自己好好再想想就出来了，
exists在写hql和jdbc的时候从来没有用过，据说少量结果集用exists,大量结果集用in，再研究研究

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in；反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists

请问： 你说的这个: 主查询中表较大又有索引时应该用In;反之外层主查询记录较少，子查询表大又有索引时用exists是有实践证明 ，还是某本书某个老师说的

25 楼 Mrpublic 2010-08-18  

finux 写道

呵呵。。。发下我的想法，不一定正名哦~
1.
select username from user usr
left join article art on art.userid = user.userid
where art.userid is null;

可以把On 与 where 在一起用哦？？ 求解

我只见过... left join ... on ...没有见过 后面还有一个where,
本人菜鸟，给解...

24 楼 facade 2010-08-17  

查询:

(1)
SELECT U.USERNAME FROM USER U WHERE NOT EXISTS
(SELECT 1 FROM ARTICLE A
 WHERE U.USERID = A.USERID) 
 
(2)
SELECT TITLE FROM ARTICLE A LEFT JOIN VOTE V 
 ON A.ARTICLEID = V.ARTICLEID
 WHERE V.SCORE > 100 
 ORDER BY V.SCORE DESC

(3)
SELECT USERID,COUNT(1) AS TOTAL, AVG(SCORE) AS AVG_SCORE 
 FROM ARTICLE A LEFT JOIN VOTE V
      ON A.ARTICLEID = V.ARTICLEID
 GROUP BY USERID
 HAVING AVG(SCORE) > 100  AND COUNT(1) > 5
 ORDER BY AVG_SCORE DESC

23 楼 lerous 2010-08-17  

kakaluyi 写道

谢谢，第3个题目看来只要自己好好再想想就出来了，
exists在写hql和jdbc的时候从来没有用过，据说少量结果集用exists,大量结果集用in，再研究研究

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in；反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists

22 楼 finux 2010-08-17  

呵呵。。。发下我的想法，不一定正确哦~
1.
select username from user usr
left join article art on art.userid = user.userid
where art.userid is null;

2.
select art.title, vt.score from article art
left join vote vt on vt.articleid = art.articleid
where vt.score > 100 order by vt.score desc;

3.
--貌似把问题给复杂化了，呵呵。。。
select usr.* from user usr
inner join
(select art.userid userid, avg(vt.score) aavg from article art
inner join vote vt on vt.articleid = art.articleid
where aavg > 100 group by art.userid) tmp1 on tmp1.userid = usr.userid
inner join
(select userid userid, count(articleid) cart
from article where cart > 5 group by userid) tmp2 on tmp2.userid = usr.userid
where tmp1.userid = tmp2.userid order by tmp1.aavg desc;

21 楼 mercyblitz 2010-08-17  

gezi1003 写道

第三题：
select b.userid usId, a.usernmae, avg(v.score) aScore, count(1) aCount
   from user_t a, article b, vote v
  where a.userid = b.userid
    and v.articleid = b.articleid having avg(v.score) > 100
    and count(b.articleid) > 5
  group by b.userid, a.usernmae
  order by aScore;

第五题等待高手回答。

mercyblitz 写道

1000W 用户，使用100w作为一个表，users_1,...users_10。

类似文章表类似。

粒度可以有App控制，一般Shard是用逻辑App来做的。

20 楼 gezi1003 2010-08-17  

第三题：
select b.userid usId, a.usernmae, avg(v.score) aScore, count(1) aCount
   from user_t a, article b, vote v
  where a.userid = b.userid
    and v.articleid = b.articleid having avg(v.score) > 100
    and count(b.articleid) > 5
  group by b.userid, a.usernmae
  order by aScore;

第五题等待高手回答。

19 楼 wang_luo_xiaozi 2010-08-17  

对于第三题，大家都忽略了排序，我的答案如下：
select u.userName from User u inner join
(select userId, avg(score) avgCount
   from Article a
   inner join Vote v on a.articleId=v.articleId
   group by a.userId
   having count(articleId)>5 and avg(score)>100) t on t.userId=u.userId
order by t.avgCount

18 楼 wang_luo_xiaozi 2010-08-17  

对于第三题，大家都忽略了排序，我的答案如下：
select u.userName from User u inner join
(select userId, avg(score)
   from Article a
   inner join Vote v on a.articleId=v.articleId
   group by a.userId
   having count(articleId)>5 and avg(score)>100) t on t.userId=u.userId
order by t.avgCount

17 楼 mercyblitz 2010-08-17  

niwei 写道

第五题呢？

1000W 用户，使用100w作为一个表，users_1,...users_10。

类似文章表类似。

16 楼 niwei 2010-08-17  

第五题呢？

15 楼 robin_hood 2010-08-17  

baidu的笔试题目

14 楼 snake1987 2010-08-17  

lkj107 写道

第一个，俺会用外连接，然后 is null

exists的性能比外连接更好，曾用过做数十万数据量的查询，exists大概比外连接快30%左右

13 楼 kakaluyi 2010-08-17  

mercyblitz 写道

楼主喜欢用子查询哦，利用外连接速度会更好！

huzhenyu 写道

只说一点，能用左连接做掉的事情，尽量不要去用exists,in之类的

谢谢，确实~

12 楼 huzhenyu 2010-08-17  

只说一点，能用左连接做掉的事情，尽量不要去用exists,in之类的

11 楼 yipbxx 2010-08-17  

我是来围观第五个问题的答案的。

10 楼 hedajia 2010-08-17  

cy729215495 写道

1.显然用exists效率高
3.select * from user where  exists (
select userid from Article inner join vote on article.articleid
= vote.articleid  where user.id=article.userId  group by userId  having avg(score)>100 and count(*) >=2
)

探讨一下
  我觉得你的exists中的子查询：
select userid
from Article
inner join vote on article.articleid = vote.articleid 
where user.id=article.userId 
group by userId 
having avg(score)>100 and count(*) >=2
有问题，原因如下：
Article 表 inner join vote 表，则把已发表的文章但是未投票的文章所对应的记录过滤掉了，
所以你count(*) 计算以发表的文章数时，会比实际的少。不知是否有考虑到？

9 楼 yinxiangbing 2010-08-17  

第三题：
select
user.userName
from user
inner join
(
    --平均得票数大于100的用户
select  art.userId
from  article art
inner join vote on art.articleId = vote.articleId
where art.userId in(
            --文章数大于5的用户
select  userId
from  article
group by userId
having count(articleId) > 5
)
group by art.userId
having avg(vote.score) > 100
) newuser on user.userId =newuser.userId

8 楼 andyivy6 2010-08-17  

mark.sql方面的学习需要加强

7 楼 hedajia 2010-08-17  

lkj107 写道

第一个，俺会用外连接，然后 is null

+1