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: 0mysql>
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)
以上数据我写的SQL语句select rownum from
(set @x=0;select @x:=ifnull(@x,0)+1 as rownum,id from tbl)
再我的mysql中写的出问题了
SQL 查询:(
SET @x =0;MySQL 返回:文档
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @x=0' at line 2 错误出现再哪里我这种用法是否正确
-> 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: 0mysql>
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)
以上数据我写的SQL语句select rownum from
(set @x=0;select @x:=ifnull(@x,0)+1 as rownum,id from tbl)
再我的mysql中写的出问题了
SQL 查询:(
SET @x =0;MySQL 返回:文档
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @x=0' at line 2 错误出现再哪里我这种用法是否正确
http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/20/4095531.aspx
MySQL中的ROWNUM的实现
as
select @x:=ifnull(@x,0)+1 as rownum,id from tbl;select * from t1_temp;
select rownum from(select
@x:=ifnull(@x,0)+1 as rownum,
sanka.sei,
sanka.mei,
sanka.sex,
result.avg_handicap + result.scrach_score + result.agesex_handicap as handicap,
result.classfrom t_ab_tour_result result
inner join m_sanka sanka
on
result.m_ab_player_seq=sanka.m_sanka_seqorder by handicap desc) as aaa我把 set @x=0; 放到最上边居然好用顺便再问个问题这二个算是一句么????
select @x:=ifnull(@x,0)+1 as rownum
from t_ab_tour_result result inner join m_sanka sanka
on result.m_ab_player_seq=sanka.m_sanka_seq
order by handicap desc;