首先取得表的总数 @total_count ; 然后 用动态语句取:select count(*) into @total_count from tb_name; set @i=floor(@total_count/4); set @str = concat('select * from tb_name limit 0,',@i); prepare stmt1 from @str; execute stmt1; deallocate prepare stmt1;
select count(*) into @total_count from tb_name; set @i=floor(@total_count/4); select * from tb_name limit 0,@i;
mysql> select count(*) from tc; +----------+ | count(*) | +----------+ | 201 | +----------+ 1 row in set (0.00 sec)mysql> select count(*) from tc a -> where (select count(*) from tc)/4>=(select count(*) from tc where id<a.id); +----------+ | count(*) | +----------+ | 51 | +----------+ 1 row in set (0.02 sec)mysql>效率很差的一种方法?建议这种功能应该在程序中实现而不是SQL语句中来实现。通过程序得到总数 N count(*) 然后直接 limit N
TRY: 假设表中只有一个字段 ID SELECT A.ID FROM LSB2 A LEFT JOIN LSB2 B ON A.ID>=B.ID GROUP BY A.id HAVING COUNT(B.id)<=CEIL((SELECT COUNT(ID) FROM LSB2)/4)
首先取得表的总数 @total_count ;
然后
用动态语句取:select count(*) into @total_count from tb_name;
set @i=floor(@total_count/4);
set @str = concat('select * from tb_name limit 0,',@i);
prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
select count(*) into @total_count from tb_name;
set @i=floor(@total_count/4);
select * from tb_name limit 0,@i;
+----------+
| count(*) |
+----------+
| 201 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from tc a
-> where (select count(*) from tc)/4>=(select count(*) from tc where id<a.id);
+----------+
| count(*) |
+----------+
| 51 |
+----------+
1 row in set (0.02 sec)mysql>效率很差的一种方法?建议这种功能应该在程序中实现而不是SQL语句中来实现。通过程序得到总数 N count(*) 然后直接 limit N
假设表中只有一个字段 ID
SELECT A.ID FROM LSB2 A LEFT JOIN LSB2 B ON A.ID>=B.ID GROUP BY A.id
HAVING COUNT(B.id)<=CEIL((SELECT COUNT(ID) FROM LSB2)/4)