mysql 自增 记录在哪里_mysql 自增变量 行标 记录位置

SELECT * FROM

(SELECT ID,(@i:=@i+1) AS i FROM sps_posts,(SELECT @i:=0) AS it WHERE post_status='publish') temp

实际应用：

/* get cat post pagination position

----------------------------------------------------------------- */

function get_cat_post_position($cat,$post_id){

global $wpdb;

$sql = "SELECT i FROM

(SELECT ID,(@i:=@i+1) AS i

FROM (SELECT ID

FROM {$wpdb->prefix}posts AS p

INNER JOIN $wpdb->term_relationships AS tr ON ( p.ID=tr.object_id )

INNER JOIN $wpdb->term_taxonomy AS tt ON ( tr.term_taxonomy_id=tt.term_taxonomy_id )

WHERE post_status='publish'

AND post_type='post'

AND tt.term_id=$cat

GROUP BY ID

ORDER BY post_date DESC) tp

,(SELECT @i:=0) AS it

) temp

WHERE ID=$post_id";

return $wpdb->get_var($sql);

}

/* get cat post pagination position page

----------------------------------------------------------------- */

function get_cat_post_page($cat,$post_id){

$pageSize = 10;

$total = get_cat_post_position($cat,$post_id);

$page =(int)(($total+$pageSize-1)/$pageSize);

return $page ;

}