MySQL-一个sql语句，关于两张表，一对多，且两个都取前n个记录

Question

两张表：
question (id,hot)
answer (id,qid,hot) 其中qid为外键，指向question的id

其中hot为question的火热程度或answer的火热程度。

现在要取出最火的10个question，且每个question带有他们各自的最火的3个answer

请问该怎么写？

数据库为mysql

先谢谢大家了

Answer 1

如果需要的结果集不大的话, 建议分成很多小的查询, 先查 前n个问题, 再按每个问题查询, 这样sql非常简单的. 是否用多个简单查询代替单个复杂查询, 参见<<高性能mysql>> Complex Queries Versus Many Queries
"However, this advice doesn't apply as much to MySQL, because it was designed to
handle connecting and disconnecting very efficiently and to respond to small and simple queries very quickly. Modern networks are also significantly faster than they used to be, reducing network latency. Depending on the server version, MySQL can run well over 100,000 simple queries per second on commodity server hardware and over 2,000 queries per second from a single correspondent on a gigabit network, so running multiple queries isn't necessarily such a bad thing."

如果一定要一个sql搞定的也可以:

set @rownum = 0;
set @rowtype = -1;

select q_id, a_id
from
( select q_id, a_id,
( case q_id
when @rowtype
then @rownum := @rownum + 1
else @rownum := 0 end
) rownum,
(@rowtype := q_id) dummy
from
(select a.qid as q_id, a.id as a_id, hot
from answer a,
(select id from question order by hot desc limit 10) q1
where q1.id = a.qid
order by a.qid, a.hot desc
) t
) tmp
where rownum < 3;

Answer 2

数据库是什么。不同数据库写法不一样