56,682
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY emp.c_uid ASC) AS RowID, emp.c_uid, emp.c_name_c, emp.c_name_e, emp.c_user_type,emp.i_status,emp.d_create FROM tbl_user emp) as list WHERE RowID between (1-1) * 10 + 1 and 1*10 select count(*) from (SELECT ROW_NUMBER() OVER (ORDER BY emp.c_uid ASC) AS RowID, emp.c_uid, emp.c_name_c, emp.c_name_e, emp.c_user_type,emp.i_status,emp.d_create FROM tbl_user emp) as tmp
string sql="select * from tbl_user order by c_uid limit 80,10";
mysql> select * from tbl_user;
+--------+------------+----------+----------------------------------+----------+----------+---------
-+---------------------+--------------+---------------------+-------------+
| c_uid | c_name_c | c_name_e | c_pw | i_status | i_pw_qid | c_pw_ans
| d_create | c_modify_uid | d_modify | c_user_type |
+--------+------------+----------+----------------------------------+----------+----------+---------
-+---------------------+--------------+---------------------+-------------+
| admin | 超级管理员 | NULL | 123456 | 0 | NULL | NULL
| 2009-05-02 13:00:25 | NULL | 0000-00-00 00:00:00 | NULL |
| 123 | 123 | 123 | 202CB962AC59075B964B07152D234B70 | 1 | 2 | 123
| 2009-05-03 21:12:19 | NULL | 0000-00-00 00:00:00 | NULL |
| aaa111 | aaa111 | aaa111 | EABD8CE9404507AA8C22714D3F5EADA9 | 1 | 1 | aaa111
| 2009-05-04 09:18:48 | NULL | 0000-00-00 00:00:00 | A |
| aa1234 | ?? | nihao | E10ADC3949BA59ABBE56E057F20F883E | 1 | 1 | asdf
| 2009-05-04 12:02:14 | NULL | 0000-00-00 00:00:00 | A |
+--------+------------+----------+----------------------------------+----------+----------+---------
-+---------------------+--------------+---------------------+-------------+
public static string PagerSql(string oldsql, int PageIndex, int PageSize, string strOrder)
{
Regex se = new Regex(@"^\s*select", RegexOptions.IgnoreCase);
oldsql = se.Replace(oldsql, "SELECT ROW_NUMBER() OVER (ORDER BY " + strOrder + ") AS RowID,");
string sql = "SELECT * FROM "
+ " (" + oldsql + ") as list "
+ " WHERE RowID between (" + PageIndex + "-1) * " + PageSize + " + 1 and " + PageIndex + "*" + PageSize;
sql += " select count(*) from (" + oldsql + ") as tmp";
return sql;
}
mysql> select c_uid,c_name_c,c_name_e,i_status,c_user_type,d_create from tbl_us
er;
+--------+------------+----------+----------+-------------+---------------------
+
| c_uid | c_name_c | c_name_e | i_status | c_user_type | d_create
|
+--------+------------+----------+----------+-------------+---------------------
+
| admin | 超级管理员 | NULL | 0 | NULL | 2009-05-02 13:00:25
|
| 123 | 123 | 123 | 1 | NULL | 2009-05-03 21:12:19
|
| aaa111 | aaa111 | aaa111 | 1 | A | 2009-05-04 09:18:48
|
| aa1234 | ?? | nihao | 1 | A | 2009-05-04 12:02:14
|
+--------+------------+----------+----------+-------------+---------------------
+
4 rows in set (0.00 sec)
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY emp.c_uid ASC) AS RowID,
emp.c_uid, emp.c_name_c, emp.c_name_e,
emp.c_user_type,emp.i_status,emp.d_create
FROM tbl_user emp
) as list
WHERE RowID between (1-1) * 10 + 1 and 1*10
SELECT emp.c_uid, emp.c_name_c, emp.c_name_e,
emp.c_user_type,emp.i_status,emp.d_create
FROM tbl_user emp
order by emp.c_uid ASC
limit 1,10;
mysql> select * from tbl_user;
+--------+------------+----------+----------------------------------+----------+
----------+----------+---------------------+--------------+---------------------
+-------------+
| c_uid | c_name_c | c_name_e | c_pw | i_status |
i_pw_qid | c_pw_ans | d_create | c_modify_uid | d_modify
| c_user_type |
+--------+------------+----------+----------------------------------+----------+
----------+----------+---------------------+--------------+---------------------
+-------------+
| admin | 超级管理员 | NULL | 123456 | 0 |
NULL | NULL | 2009-05-02 13:00:25 | NULL | 0000-00-00 00:00:00
| NULL |
| 123 | 123 | 123 | 202CB962AC59075B964B07152D234B70 | 1 |
2 | 123 | 2009-05-03 21:12:19 | NULL | 0000-00-00 00:00:00
| NULL |
| aaa111 | aaa111 | aaa111 | EABD8CE9404507AA8C22714D3F5EADA9 | 1 |
1 | aaa111 | 2009-05-04 09:18:48 | NULL | 0000-00-00 00:00:00
| A |
| aa1234 | ?? | nihao | E10ADC3949BA59ABBE56E057F20F883E | 1 |
1 | asdf | 2009-05-04 12:02:14 | NULL | 0000-00-00 00:00:00
| A |
+--------+------------+----------+----------------------------------+----------+
----------+----------+---------------------+--------------+---------------------
+-------------+
4 rows in set (0.03 sec)
mysql> create table tb(name varchar(30),age int);
Query OK, 0 rows affected (1.27 sec)
mysql> insert tb
-> select 'A',18 union all
-> select 'B',19 union all
-> select 'C',22 union all
-> select 'D',17;
Query OK, 4 rows affected (1.34 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> set @mycnt = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from (
-> select (@mycnt := @mycnt + 1) as ROWNUM,name,age
-> from tb where AGe < 20 order by name,age
-> ) as A;
+--------+------+------+
| ROWNUM | name | age |
+--------+------+------+
| 1 | A | 18 |
| 2 | B | 19 |
| 3 | D | 17 |
+--------+------+------+
3 rows in set (0.00 sec)
select *
from tb
limit X,Y