如题: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我有段这这样的SQL代码,但是在MYSQL中不支持 ROW_NUMBER(),请问在MYSQL中有什么方法可以替代????
解决方案 »
- mysql找出大于今天的日期
- 实在没法儿了,怎么在mysql中根据成绩排名并更新到排名的那个字段
- PowerDesigner 问题请教
- postgresSQL下游标相关问题
- mysql中修改字段属性语句是如何写的呀
- 无头绪--跨表区域统计查询
- 怎样使用mysqlimport -u root --password=taiji tjlog< tjmysqldump
- 怎样将MSsql server2000中的数据库直接生成MySQL的数据库
- 写好的sql脚本怎么让他执行(命令行界面下)
- 请教关于MySQL新增数据,不存在则新增,存在则更新的问题
- mysql如何存储藏文
- 上网查找了好久没有解决的问题,请赐教!!!
from tb
limit X,Y
http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/20/4095531.aspx
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: 0mysql> 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)
+--------+------------+----------+----------------------------------+----------+
----------+----------+---------------------+--------------+---------------------
+-------------+
| 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)
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 如果只是想取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 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)
想要的结果
{
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;
}
set @ff=concat('select c_uid,c_name_c,c_name_e,i_status,c_user_type,d_create from tbl_us limit ',cs1,',',cs2)
prepare stmt1 from @ff;
EXECUTE stmt1;
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 |
+--------+------------+----------+----------------------------------+----------+----------+---------
-+---------------------+--------------+---------------------+-------------+
表的结果和数据
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#select
13.2.7. SELECT语法LIMIT子句可以被用于限制被SELECT语句返回的行数。LIMIT取一个或两个数字自变量,自变量必须是非负的整数常数(当使用已预备的语句时除外)。使用两个自变量时,第一个自变量指定返回的第一行的偏移量,第二个自变量指定返回的行数的最大值。初始行的偏移量为0(不是1):mysql> SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15为了与PostgreSQL兼容,MySQL也支持LIMIT row_count OFFSET offset语法。如果要恢复从某个偏移量到结果集合的末端之间的所有的行,您可以对第二个参数是使用比较大的数。本语句可以恢复从第96行到最后的所有行:mysql> SELECT * FROM tbl LIMIT 95,18446744073709551615;使用1个自变量时,该值指定从结果集合的开头返回的行数:mysql> SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows换句话说,LIMIT n与LIMIT 0,n等价。对于已预备的语句,您可以使用位置保持符。以下语句将从tb1表中返回一行:mysql> SET @a=1;mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";
mysql> EXECUTE STMT USING @a;以下语句将从tb1表中返回第二到第六行:mysql> SET @skip=1; SET @numrows=5;mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
mysql> EXECUTE STMT USING @skip, @numrows;
> mysql> SET @skip=1; SET @numrows=5;mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
mysql> EXECUTE STMT USING @skip, @numrows;
但是如果我要在代码中去控制写而不是直接在MYSQL中执行呢(如string sql="......";),使用位置保持符也一样吗?