我看只能这样: select if ( count(*)>20, 20, count(*) ) as Number from tbl_name1 where username='white' limit 0, 20 反正当所有符合条件的记录数大于20的时候,查询出来的结构就是20条,用这个应该可以吧。
select count(*) from (select * from tbl_name1 limit 0,20) as a;
Arbow的方法对我的mysql版本是3.23.44,运行select count(*) from tbl_name1 where username='white' limit 0, 20总是返回所有满足条件的记录数
select count(*) from (select * from tbl_name1 where username='white' limit 2,1 ) as a; 我用的是psql;是好用的。 table Test=# select * from test1 ; company_code | update_date | input_money --------------+-------------+------------- 01 | 2003/03 | 10000 01 | 2003/04 | 20000 01 | 2003/05 | 30000 01 | 2003/06 | 40000 01 | 2003/07 | 20000 02 | 2003/02 | 20000 02 | 2003/03 | 20000 02 | 2003/04 | 20000 02 | 2003/05 | 30000 (9 rows)Test=# select * from (select * from test1 where company_code='02' limit 2,1 ) as a; company_code | update_date | input_money --------------+-------------+------------- 02 | 2003/03 | 20000 02 | 2003/04 | 20000 (2 rows) Test=# select count(*) from (select * from test1 where company_code='01' limit 2,4) as a; count ------- 1 (1 row) Test=# select * from (select * from test1 where company_code='01' limit 2,4 ) as a; company_code | update_date | input_money --------------+-------------+------------- 01 | 2003/07 | 20000 (1 row) Test=# select count(*) from (select * from test1 where company_code='01' limit 2,5 ) as a; count ------- 0 (1 row)Test=# select * from (select * from test1 where company_code='01' limit 2,5 ) as a; company_code | update_date | input_money --------------+-------------+------------- (0 rows)
select if ( count(*)>20, 20, count(*) ) as Number from tbl_name1 where username='white' limit 0, 20
反正当所有符合条件的记录数大于20的时候,查询出来的结构就是20条,用这个应该可以吧。
我用的是psql;是好用的。
table
Test=# select * from test1 ;
company_code | update_date | input_money
--------------+-------------+-------------
01 | 2003/03 | 10000
01 | 2003/04 | 20000
01 | 2003/05 | 30000
01 | 2003/06 | 40000
01 | 2003/07 | 20000
02 | 2003/02 | 20000
02 | 2003/03 | 20000
02 | 2003/04 | 20000
02 | 2003/05 | 30000
(9 rows)Test=# select * from (select * from test1 where company_code='02' limit 2,1 ) as a;
company_code | update_date | input_money
--------------+-------------+-------------
02 | 2003/03 | 20000
02 | 2003/04 | 20000
(2 rows)
Test=# select count(*) from (select * from test1 where company_code='01' limit 2,4) as a;
count
-------
1
(1 row)
Test=# select * from (select * from test1 where company_code='01' limit 2,4 ) as a;
company_code | update_date | input_money
--------------+-------------+-------------
01 | 2003/07 | 20000
(1 row)
Test=# select count(*) from (select * from test1 where company_code='01' limit 2,5 ) as a;
count
-------
0
(1 row)Test=# select * from (select * from test1 where company_code='01' limit 2,5 ) as a;
company_code | update_date | input_money
--------------+-------------+-------------
(0 rows)