(SELECT @num := (SELECT COUNT(0) FROM test AS b WHERE b.kid = a.kid AND b.mid >= a.mid) AS num,
a.ver,a.kid,a.mid
FROM test AS a) AS c
WHERE c.num <= 3
ORDER BY c.kid DESC, c.mid DESC;
复制代码
作者: kider 时间: 2013-5-8 14:03
附表结构及数据:
CREATE TABLE `test` (
`ver` varchar(10) DEFAULT NULL,
`kid` int(11) DEFAULT NULL,
`mid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `test`(`ver`,`kid`,`mid`) values ('0.0.1',203,29),('0.0.1',203,4),('0.0.1',203,38),('0.0.1',203,86),('0.0.1',203,42),('0.0.1',203,546),('0.0.1',203,34),('0.0.1',204,15),('0.0.1',204,20),('0.0.1',204,371),('0.0.1',204,78),('0.0.1',204,57),('0.0.1',204,38),('0.0.1',204,960),(NULL,NULL,0);
嗯不错不错,能不能分别控制第二列(kid)和第三列(mid)的个数呢?我测试了一下,修改这个数字(c.num <= 4 )可以控制第三列的个数,但是第二列我如果想让它显示三个不同的项,那该怎么办
(例如:
ver kid mid
0.0.1 204 960
0.0.1 204 371
0.0.1 203 546
0.0.1 203 86
0.0.1 202 119
0.0.1 202 78
)作者: kider 时间: 2013-5-9 17:48
{:soso_e110:}作者: 小苏 时间: 2013-5-9 18:04
加个条件GROUP BY一个kid然后TOP一下个数作者: wangwl_s 时间: 2013-5-31 13:37
我也回复个
SELECT
t.ver,t.kid,t.mid FROM test t
WHERE kid IS NOT NULL AND 3 > (SELECT COUNT(1) FROM test WHERE kid = t.kid AND MID > t.mid)
ORDER BY kid DESC, MID desc作者: 蓝寅舞九天 时间: 2013-7-14 12:15
周末刷论坛玩吧。。
SELECT a.ver,a.kid,a.mid
FROM test a inner join
test b on a.ver =b.ver and a.kid=b.kid and a.mid<=b.mid
group by a.ver,a.kid,a.mid
having count(*)<=3
order by a.ver, a.kid desc, a.mid desc
;作者: rickly0012 时间: 2014-11-17 09:46
还是需要自己动手操作一下,有点关注执行性能了。作者: beer2008cn 时间: 2014-12-12 10:53