表Asn states tableid
0 0 1255024
0 0 1526481
0 0 1897542
1 0 1987458
1 0 1254875
1 1 1655884
2 2 1689556
2 0 1874257
2 1 1154882
...想获得的结果每个sn的前2行sn states tableid
0 0 1255024
0 0 1526481
1 0 1987458
1 0 1254875
2 2 1689556
2 0 1874257
...求MySQL的解决方法。
0 0 1255024
0 0 1526481
0 0 1897542
1 0 1987458
1 0 1254875
1 1 1655884
2 2 1689556
2 0 1874257
2 1 1154882
...想获得的结果每个sn的前2行sn states tableid
0 0 1255024
0 0 1526481
1 0 1987458
1 0 1254875
2 2 1689556
2 0 1874257
...求MySQL的解决方法。
select * from A t
where 2>
(select count(*) from A
where sn=t.sn and tableid<t.tableid);
LEFT JOIN test1 b
ON a.sn=b.sn AND a.tableid <=b.tableid
GROUP BY a.sn,a.states,a.tableid
HAVING COUNT(b.sn) <=2
http://topic.csdn.net/u/20091231/16/5c5fd526-b2bc-42bf-9a20-92b82b1e9452.html
select a.sn,a.states,a.tableid from tt a left join tt b
on a.sn=b.sn and a.id>=b.id
group by a.sn,a.states,a.tableid
having count(b.id)<=2
SELECT a.sn,a.states,a.tableid FROM tt a
WHERE 2>=(
SELECT COUNT(*) FROM tt b
WHERE a.sn=b.sn and a.id>=b.id)
实际所有的想法都是为了提高速度,这些数据都是按照一定的规则排序以后生成的视图,之所以想取出每个分组的前N条记录,就是为了获得满足条件的tableid,实现对实体表数据的批量删除。如果我在程序中用sn一个个迭代查找,性能也不会高。在实际中,想把这个操作做成一个job,但是还需要获得上司的认可啊~~
ELECT DISTINCT a2.* FROM tt6 a2 INNER JOIN (
SELECT *,(SELECT tableid FROM tt6 b WHERE b.sn=a.sn LIMIT 1) AS n1,
(SELECT tableid FROM tt6 b WHERE b.sn=a.sn LIMIT 1,1) AS n2
FROM tt6 a) a3 ON a2.sn=a3.sn AND (a2.tableid IN(a3.n1,a3.n2))看看速度如何
所以还不如用程序或存储过程, select * from 表A order by sn , tableid 然后在程序中只能第个SN的前两个。这样只是一次全表扫描。速度上应该最快。