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

第三题：
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;

第五题等待高手回答。

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

呵呵。。。发下我的想法，不一定正确哦~
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;

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

查询:

(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

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

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

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

帅多，你的这个 1是什么意思呀?没见过，请给分析一下...

这种说法似乎是误传...

这种东西基本都是误传。