通过添加一条行记录的方法,重新生成一个表,然后在通过可以通过表的连接进行运算。
--通过一个初始值为0的变量@rownum,依次递增1来实现行号
SELECT
@rownum:=@rownum+1 AS rownum,name
FROM temp,
(SELECT @rownum:=0) temp
WHERE @rownum<4(条件,没有也可以);
给出一般性的语句:
SELECT
@rownum:=@rownum+1 AS rownum,所需字段
FROM 表名,
(SELECT @rownum:=0) 表名
WHERE @rownum<N(获取多少行);
然后给出测试案例:
第一步,创建测试表;
mysql> CREATE TABLE temp(id INT(4),name VARCHAR(20));
Query OK, 0 rows affected (0.13 sec)
第二步,插入测试数据;
mysql> INSERT INTO temp VAlUES(1,'robin');
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO temp VAlUES(2,'wentasy');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO temp VAlUES(3,'justdb');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO temp VAlUES(4,'wen');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 16 rows affected (0.02 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 32 rows affected (0.03 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 64 rows affected (0.04 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 128 rows affected (0.03 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 256 rows affected (0.04 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 512 rows affected (0.05 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> INSERT INTO temp SELECT * FROM temp;
Query OK, 1024 rows affected (0.05 sec)
Records: 1024 Duplicates: 0 Warnings: 0
第三步,实现类似Oracle中的rownum效果;
mysql> SELECT
-> @rownum:=@rownum+1 AS rownum,name
-> FROM temp,
-> (SELECT @rownum:=0) temp
-> WHERE @rownum<4;
+--------+---------+
| rownum | name |
+--------+---------+
| 1 | robin |
| 2 | wentasy |
| 3 | justdb |
| 4 | wen |
+--------+---------+
4 rows in set (0.01 sec)
---------------------------------------------------------------------------------------------------
下面介绍几种具体的实现方法.
建立实验环境如下
mysql> create table tbl (
-> id int primary key,
-> col int
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into tbl values
-> (1,26),
-> (2,46),
-> (3,35),
-> (4,68),
-> (5,93),
-> (6,92);
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from tbl order by col;
+----+------+
| id | col |
+----+------+
| 1 | 26 |
| 3 | 35 |
| 2 | 46 |
| 4 | 68 |
| 6 | 92 |
| 5 | 93 |
+----+------+
6 rows in set (0.00 sec)
1. 直接在程序中实现;
这应该算是效率最高的一种,也极为方便。直接在你的开发程序中(PHP/ASP/C/...)等中,直接初始化一个变量nRowNum=0,然后在while 记录集时,nRowNum++; 然后输出即可。
2. 使用MySQL变量;在某些情况下,无法通过修改程序来实现时,可以考虑这种方法。
缺点,@x 变量是 connection 级的,再次查询的时候需要初始化。一般来说PHP等B/S应用没有这个问题。但C/S如果connection一只保持则要考虑 set @x=0
mysql> select @x:=ifnull(@x,0)+1 as rownum,id,col
-> from tbl
-> order by col;
+--------+----+------+
| rownum | id | col |
+--------+----+------+
| 1 | 1 | 26 |
| 1 | 3 | 35 |
| 1 | 2 | 46 |
| 1 | 4 | 68 |
| 1 | 6 | 92 |
| 1 | 5 | 93 |
+--------+----+------+
6 rows in set (0.00 sec)
3. 使用联接查询(笛卡尔积)
缺点,显然效率会差一些。
利用表的自联接,代码如下,你可以直接试一下 select a.*,b.* from tbl a,tbl b where a.col>=b.col 以理解这个方法原理。
mysql> select a.id,a.col,count(*) as rownum
-> from tbl a,tbl b
-> where a.col>=b.col
-> group by a.id,a.col;
+----+------+--------+
| id | col | rownum |
+----+------+--------+
| 1 | 26 | 1 |
| 2 | 46 | 3 |
| 3 | 35 | 2 |
| 4 | 68 | 4 |
| 5 | 93 | 6 |
| 6 | 92 | 5 |
+----+------+--------+
6 rows in set (0.00 sec)
4. 子查询
缺点,和联接查询一样,具体的效率要看索引的配置和MySQL的优化结果。
mysql> select a.*,
-> (select count(*) from tbl where col<=a.col) as rownum
-> from tbl a;
+----+------+--------+
| id | col | rownum |
+----+------+--------+
| 1 | 26 | 1 |
| 2 | 46 | 3 |
| 3 | 35 | 2 |
| 4 | 68 | 4 |
| 5 | 93 | 6 |
| 6 | 92 | 5 |
+----+------+--------+
6 rows in set (0.06 sec)
做为一款开源的数据库系统,MySQL无疑是一个不做的产品。它的更新速度,文档维护都不逊于几大商业数据库产品。估计在下一个版本中,我们可以看到由MySQL自身实现的ROWNUM。
----------------------------------------------------------------------------------------------------
应用:同一个表中相邻的两条记录进行运算中的cpu_busy,cpu_total。
select usr+nice+sys+idle+iowait+irq+softirq ascpu_totalfrom os;
select usr+nice+sys+iowait+irq+softirq as cpu_busy from os;
思路:os表和os表进行自身的左连接操作,条件是表A的A.timestamp_+1等于表B的B.timestamp_。
select A.timestamp_, A.cpu_total , A.cpu_busy,
B.timestamp_ , B.cpu_total , B.cpu_busy, B.cpu_busy-A.cpu_busy ,B.cpu_total-A.cpu_total,
(B.cpu_busy-A.cpu_busy)/(B.cpu_total-A.cpu_total) as cpu_usage from
(select timestamp_, usr+nice+sys+idle+iowait+irq+softirq as cpu_total ,usr+nice+sys+iowait+irq+softirq as cpu_busy from os ) A
left join
(select timestamp_, usr+nice+sys+idle+iowait+irq+softirq as cpu_total ,usr+nice+sys+iowait+irq+softirq as cpu_busy from os ) B
on A.timestamp_+1=B.timestamp_;
把一个表的某几项数据,插入到另一个表中。
UPDATE 表A INNER JOIN 表B ON 表A的字段=表B的字段(条件) SET 表A的等待修改项=表B的某个字段
update os inner join
(select timestamp_ from ) D
on timestamp_=D.btimestamp_
set os.cpu_usage=D.cpu_usage;