Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Ҳ̸MySQLÖÐʵÏÖROWNUM

À´Ô´  http://e-xia.com/2009/06/rownum-in-mysql/
ÔÚ¹¤×÷ÖÐÅöµ½ÕâÑùµÄÎÊÌ⣬ÔÚÉú³É±¨±íʱµÚÒ»ÁÐÒªÊä³ötop 1, top 2, ... , top 10¡£¶ømysql²¢²»×Ô´øÕâÑùµÄ¹¦ÄÜ¡£¼ÙÉèÎÒÃÇÓÐÕâÑùµÄÒ»¸ö±í£º
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> 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)
ÖйæÖоصÄ×ö·¨ÊÇ£º
SET
@x=
0
;
SELECT
@x:=
@x AS
rownum,
id,
col
from
tbl
ORDER
BY
col;
µ«ÊÇÕâÑù¾Í±ä³ÉÁËÁ½¸öquery£¬ÔÚjavaÀïÃæÓÃexecuteQuery»áÓÐÎÊÌâ¡£
µ±Ê±×Ô¼ºÏëµ½µÄÊÇÕâÑù£º
SELECT
@x :=
IFNULL(
@x,
0
)
+
1
AS
rownum,
id,
col
from
tbl
ORDER
BY
col;
µ«ÊǵÚÒ»´ÎÔËÐеÄʱºòrownum¶¼ÊÇ1£¬µÚ¶þ´ÎÔËÐÐrownum±ä³É2-11£¬µÚÈý´ÎΪ12-21¡£
ºóÀ´ÓÖ¿´µ½Ò»Ð©ºÜÐü£¬¶øÇÒ¿´ÉÏÈ¥ºÜûÓÐЧÂʵķ½·¨£¬ÀýÈ磺
ʹÓÃÁª½Ó²éѯ£¨µÑ¿¨¶û»ý£©
SELECT
a.
id,
a.
col,
COUNT(
*
)
AS
rownum
from
tbl a,
tbl b
WHERE
a.
col>=
b.
col
GROUP
BY
a.
id,
a.
col;
×Ó²éѯ
SELECT
a.*,
(
SELECT
count(
*
)
from
tbl WHERE
col<=
a.
col)
AS
rownum
from
tbl a;
ÕâЩ¶¼²»ÊÇÎÒÒªµÄ£¡£¡
×îºóÎÒÕÒµ½Á˵ÚÒ»ÖÖ·½·¨µÄ¸ÄÁ¼Ë¼Â·£¬×öÁËһЩ¸Ä¶¯£¬ËäÈ»»¹ÊÇÈÆÁËÒ»¸öСÍ䣬µ«ÊÇÒѾ­ºÜºÃÓÃÁË£º
SELECT
@rownum:=
@rownum+1
rownum,
id,
col
from

(
SELECT
@rownum:=
0
,
*

from
tbl
ORDER
BY
col DESC
)
t;
¸ü½øÒ»²½µÄÓ¦ÓÃÊÇ£¬µ±ÓÐÁ½¸ötableÒª²¢ÅÅ·ÅÔÚÒ»Æð£¬ÀýÈçÒ»¸öorder by id£¬ÁíÒ»¸öorder by
col£¬Ô­ÏȵÄ×ö·¨ÊÇдÁ½¸öquery£¬È»ºóÔÚÒ³ÃæÀï²¢ÅÅ·Å£¬²»¹ýÓÐÁËrownumÒÔºó¾Í¿ÉÒÔÓÃjoinÖ±½ÓÊä³öÍêÈ«·ûºÏÒªÇóµÄtableÁË¡£
²Î¿¼²ÄÁÏ£º
MySQL
ÖеÄROWNUMµÄʵÏÖ
How
to number rows i


Ïà¹ØÎĵµ£º

ʹÓÃrootÓû§ÔËÐÐMYSQLµÄ°²È«Òþ»¼

µ±ÄãÁ¬½ÓÒ»¸öMySQL·þÎñÆ÷ʱ£¬Äãͨ³£Ó¦¸ÃʹÓÃÒ»¸ö¿ÚÁî¡£¿ÚÁî²»ÒÔÃ÷ÎÄÔÚÁ¬½ÓÉÏ´«Êä¡£
ËùÓÐÆäËûÐÅÏ¢×÷ΪÄܱ»ËùÓÐÈ˶Á¶®µÄÎı¾±»´«Êä¡£Èç¹ûÄãµ£ÐÄÕâ¸ö£¬Äã¿ÉʹÓÃѹËõЭÒé(MySQL3.22ºÍÒÔÉϰ汾)ʹÊÂÇé±äµÃ¸üÄÑ¡£ÉõÖÁΪÁËʹһÇиü°²È«£¬ÄãÓ¦¸Ã°²×°ssh£¨¼ûhttp://www.cs.hut.fi/ssh)¡£ÓÃËû£¬ÄãÄÜÔÚÒ»¸öMySQL·þÎñÆ÷ºÍÒ»¸öMySQL¿Í» ......

linuxÏÂmysql±àÒë°²×°·½·¨±¸Íü

# tar -zxvf mysql-5.0.45.tar.gz -C /usr/src/
# cd /usr/src/mysql-5.0.45
# ./configure --prefix=/usr/local/mysql --with-charset=gbk
--localstatedir=/usr/local/mysql/data
# make
# make install
# groupadd mysql
# useradd mysql -g mysql
# cd /usr/local/mysql/bin
# ./mysql_install_db --user=mys ......

MySqlµÄ·þÎñÆô¶¯²»ÁË

ǰ¶Îʱ¼ä°²×°MySQL, ·Ç³£Ë³Àû, ºÜ¿ì¾Í°²×°ÁË, È»ºó¾­¹ý¼òµ¥µÄÅäÖÃ. ½øÈëMySQLµÄ¿ØÖÆÌ¨, ÊäÈëÃÜÂë. È»ºó¾Í˳ÀûµØ½øÈëµ½Á˹¤×÷״̬.
µ«Êǵ±ÎÒµÚ¶þÌì½øÈëMySQLµÄ¿ØÖÆÌ¨, ÊäÈëÃÜÂëʱ, µÎµÄÒ»Éùºó,Õû¸ö¿ØÖÆÌ¨¾ÍÏûʧÁË.ÖØÐÂÊÔÁ˼¸´Î, ÎÊÌâÒÀ¾É. ¾ÍÉÏÍø°Ù¶ÈÒ»ÏÂ,·¢ÏÖÓв»ÉÙÈËÓöµ½Í¬ÑùµÄÎÊÌâ, ºÜ¶àÈ˶¼ËµÊ±my.iniÅäÖôíÎó, ÓÚÊÇ´ò¿ ......

mysqlѧϰ1 ¸ÅÊö¼°ÌØÕ÷

·   ÄÚ²¿¹¹¼þºÍ¿ÉÒÆÖ²ÐÔ 
o  ÌṩÁËÊÂÎñÐԺͷÇÊÂÎñÐÔ´æ´¢ÒýÇæ¡£
--ÊÇ·ñÖ¸Èç¹ûÒª²ÉÓÃÊÂÎñ¹ÜÀí£¬±ØÐëÇл»´æ´¢ÒýÇæ£¿£¿£¿
·         Óï¾äºÍº¯Êý
DELETE¡¢INSERT¡¢REPLACEºÍUPDATE·µ»Ø¸ü¸Ä£¨Ó°Ï죩µÄÐÐÊý¡£Á¬½Óµ½·þÎñÆ÷ʱ£¬¿Éͨ¹ýÉèÖ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ