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个帖子,每次有一个帖子被浏览会写一条这样的数据。
`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个帖子,每次有一个帖子被浏览会写一条这样的数据。
(
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
)
select fid,id,count(*) as num
from t3
group by fid,idselect *
from temp A
where (select count(*) from temp B where A.fid = B.fid and A.num<=B.num)<=50
select fid,id,count(*) as num
from t3 group by fid,idselect * from Atemp A
where 50>=(select count(*) from temp B where A.fid = B.fid and B.num>=B.num)
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
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
)
+-----+-----+---------------------+
| 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>
3 6 2
3 7 2
为什么取
3 3 2
3 4 2CNT相同取TID小的?
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;