Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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


Ïà¹ØÎĵµ£º

MySQL ±¸·ÝºÍ»Ö¸´²ßÂÔ

ÔÚÊý¾Ý¿â±í¶ªÊ§»òË𻵵ÄÇé¿öÏ£¬±¸·ÝÄãµÄÊý¾Ý¿âÊǺÜÖØÒªµÄ¡£Èç¹û·¢Éúϵͳ±ÀÀ££¬Äã¿Ï¶¨ÏëÄܹ»½«ÄãµÄ±í¾¡¿ÉÄܶªÊ§×îÉÙµÄÊý¾Ý»Ö¸´µ½±ÀÀ£·¢ÉúʱµÄ״̬¡£±¾ÎÄÖ÷Òª¶ÔMyISAM±í×ö±¸·Ý»Ö¸´¡£
 
±¸·Ý²ßÂÔÒ»£ºÖ±½Ó¿½±´Êý¾Ý¿âÎļþ£¨²»ÍƼö£©
±¸·Ý²ßÂÔ¶þ£ºÊ¹ÓÃmysqlhotcopy±¸·ÝÊý¾Ý¿â£¨ÍêÈ«±¸·Ý£¬ÊʺÏСÐÍÊý¾Ý¿â±¸·Ý£©
±¸·Ý²ßÂÔ ......

MySQLÓëÊÂÎñ

MySQL5.X¶¼ÒѾ­·¢²¼ºÃ¾ÃÁË£¬µ«ÊÇ»¹ÓкܶàÈËÈÏΪMySQLÊDz»Ö§³ÖÊÂÎñ´¦ÀíµÄ£¬Õâ²»µÃ²»¹ÖËûÃÇÊǹª¹ÑÎŵ쬯äʵ£¬Ö»ÒªÄãµÄMySQL°æ±¾ Ö§³ÖBDB»òInnoDB±íÀàÐÍ£¬ÄÇôÄãµÄMySQL¾Í¾ßÓÐÊÂÎñ´¦ÀíµÄÄÜÁ¦¡£ÕâÀïÃæ£¬ÓÖÒÔInnoDB±íÀàÐÍÓõÄ×î¶à£¬ËäÈ»ºóÀ´·¢ÉúÁËÖîÈçOracleÊÕ ¹ºInnoDBµÈÁîMySQL²»Ë¬µÄÊÂÇ飬µ«ÄÇЩÉÌÒµÉϵĶ·ÕùÓë¼¼ÊõÎ޹أ¬Ï ......

mysql¸´ÖƱí

ÎÒÃǾ­³£»áÓöµ½ÐèÒª±í¸´ÖƵÄÇé¿ö£¬È罫һ¸ötable1µÄÊý¾ÝµÄ²¿·Ö×ֶθ´ÖƵ½table2ÖУ¬»òÕß½«Õû¸ötable1¸´ÖƵ½table2ÖУ¬ÕâʱºòÎÒÃǾÍҪʹÓÃSELECT INTO ºÍ INSERT INTO SELECT ±í¸´ÖÆÓï¾äÁË¡£
1.INSERT INTO SELECTÓï¾ä
      Óï¾äÐÎʽΪ£ºInsert into Table2(field1,field2,...) select value1,v ......

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

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