一个对于本人来说高度复杂的Sql，请高手们进来帮忙。

[总哈哈]

CREATE TABLE IF NOT EXISTS `t3` (
`fid` int(11) NOT NULL,
`tid` int(11) NOT NULL,
`logtime` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

--
-- 转存表中的数据 `t3`
--

INSERT INTO `t3` (`fid`, `tid`, `logtime`) VALUES
(3, 5, '2011-03-02 00:00:00'),
(3, 3, '2011-03-02 00:00:00'),
(3, 4, '2011-03-02 00:00:00'),
(3, 5, '2011-03-02 00:00:00'),
(3, 6, '2011-03-02 00:00:00'),
(3, 7, '2011-03-02 00:00:00'),
(3, 3, '2011-03-02 00:00:00'),
(3, 4, '2011-03-02 00:00:00'),
(3, 5, '2011-03-02 00:00:00'),
(3, 6, '2011-03-02 00:00:00'),
(3, 7, '2011-03-02 00:00:00'),
(3, 8, '2011-03-02 00:00:00'),
(4, 1, '2011-03-02 00:00:00'),
(4, 2, '2011-03-02 00:00:00'),
(4, 5, '2011-03-02 00:00:00');
如上 fid为板块ID, tid为帖子ID, 求每个板块帖子被浏览最多的前50个帖子，每次有一个帖子被浏览会写一条这样的数据。

[ACMAIN_CHM]

select * from
(
	select `fid`, `tid`, count(*) as cnt
	from t3 
	where group by fid`, `tid`
) a
where 50>=(
	select count(*) from (
		select `fid`, `tid`, count(*) as cnt
		from t3
		group by `fid`, `tid`
	) b where fid=a.fid and cnt>=a.cnt
)

[rucypli]

create temporary table  temp
select fid,id,count(*) as num
from t3
group by fid,id

select *
from temp A
where (select count(*) from temp B where A.fid = B.fid and A.num<=B.num)<=50

[WWWWA]

create VIEW Atemp AS
select fid,id,count(*) as num
from t3 group by fid,id

select * from Atemp A
where 50>=(select count(*) from temp B where A.fid = B.fid and B.num>=B.num)

[wuhen11987]

select fid,tid,cnt as 浏览次数 from (select fid, tid,count(*) as cnt from t3 group by fid, tid order by cnt desc limit 50) b

[总哈哈]

3楼的不好用，我试了一下，达不到要求，如果按照我给的数据，按取每个板块浏览最多的3个帖子算，最少应该出6条，而只出了3条。请更正一下，谢谢！

[总哈哈]

ACMAIN_CHM 的没法执行，我改了一下，可以执行了，但是不是我想要的结果

select * from
(
    select fid, tid, count(*) as cnt from t3 group by fid,`tid`
) a
where 3>=(
    select count(*) from (
        select `fid`, `tid`, count(*) as cnt
        from t3
        group by `fid`, `tid`
    ) b where fid=a.fid and cnt>=a.cnt
)

[总哈哈]

预期结果：
3 5 3
3 3 2
3 4 2
4 1 1
4 2 1
4 5 1

请各位再给力，谢谢！

[ACMAIN_CHM]

mysql> select * from t3;
+-----+-----+---------------------+
| fid | tid | logtime             |
+-----+-----+---------------------+
|   3 |   5 | 2011-03-02 00:00:00 |
|   3 |   3 | 2011-03-02 00:00:00 |
|   3 |   4 | 2011-03-02 00:00:00 |
|   3 |   5 | 2011-03-02 00:00:00 |
|   3 |   6 | 2011-03-02 00:00:00 |
|   3 |   7 | 2011-03-02 00:00:00 |
|   3 |   3 | 2011-03-02 00:00:00 |
|   3 |   4 | 2011-03-02 00:00:00 |
|   3 |   5 | 2011-03-02 00:00:00 |
|   3 |   6 | 2011-03-02 00:00:00 |
|   3 |   7 | 2011-03-02 00:00:00 |
|   3 |   8 | 2011-03-02 00:00:00 |
|   4 |   1 | 2011-03-02 00:00:00 |
|   4 |   2 | 2011-03-02 00:00:00 |
|   4 |   5 | 2011-03-02 00:00:00 |
+-----+-----+---------------------+
15 rows in set (0.00 sec)

mysql> select * from
    -> (
    ->     select `fid`, `tid`, count(*) as cnt
    ->     from t3
    ->     group by `fid`, `tid`
    -> ) a
    -> where 3>(
    ->     select count(*) from (
    ->      select `fid`, `tid`, count(*) as cnt
    ->      from t3
    ->      group by `fid`, `tid`
    ->     ) b where fid=a.fid and (cnt>a.cnt or cnt=a.cnt and tid<a.tid)
    -> )
    -> order by 1,3 desc,2;
+-----+-----+-----+
| fid | tid | cnt |
+-----+-----+-----+
|   3 |   5 |   3 |
|   3 |   3 |   2 |
|   3 |   4 |   2 |
|   4 |   1 |   1 |
|   4 |   2 |   1 |
|   4 |   5 |   1 |
+-----+-----+-----+
6 rows in set (0.00 sec)

mysql>

[WWWWA]

为什么没有？
3 6 2
3 7 2
为什么取
3 3 2
3 4 2

CNT相同取TID小的？

[WWWWA]

SELECT a.fid,a.tid,a.num FROM (SELECT `fid`, `tid`, COUNT(*) AS num
FROM t3
GROUP BY `fid`, `tid`) a LEFT JOIN
(SELECT `fid`, `tid`, COUNT(*) AS num
FROM t3
GROUP BY `fid`, `tid`) b
ON a.fid=b.fid AND (a.num<b.num OR (a.num=b.num AND a.tid>b.tid ))
GROUP BY a.fid,a.tid,a.num HAVING COUNT(a.num)<3 ORDER BY a.fid,a.num DESC;

[总哈哈]

大伙很给力，谢谢了，下次再过来给更多分。

[总哈哈]

不是一定要取tid小的，tid是一个无关紧要的东西