mysql语句加序号_在mysql中sql语句给查询的结果添加序号列 阿星小栈

一：第一种：

select  (@i:=@i+1)   as   i,table_name.*   from   table_name,(select   @i:=0)   as   it

例如：

(1)简单实例：select  (@i:=@i+1)  i,user_id from  spcp_user_20170803 ,(select   @i:=0)   as   it ORDER BY t.user_id desc;

(2)复杂实例：

SELECT

(@i:=@i+1) as i,hs_cd,hs_owner_persons,hs_owner_certy_num,hs_full_addr,t5.value_name as hs_type,t6.value_name as hs_owner_type,hs_use_size,hs_build_size,t2.cj_num,t2.db_num,t2.high_age_num,t2.ls_person_num,t7.value_name as person_type,t4.org_name

FROM

house_info_20170803 t1,house_person_info_20170803 t2,person_info_20170803 t3,cmp_org t4,cfg_value t5,cfg_value t6,cfg_value t7,(select @i:=0) as it

WHERE

t1.hs_owner_certy_num IN (

SELECT

hs_owner_certy_num

FROM

house_info_20170803

GROUP BY

hs_owner_certy_num

HAVING

COUNT(*) > 1

)

and t1.hs_id=t2.hs_id

and t2.hs_id=t3.old_hs_id

and t1.tt_org_id=t4.org_id

and (t1.hs_type=t5.value_cd and t5.item_cd='HS_TYPE')

and (t1.hs_owner_type=t6.value_cd and t6.item_cd='HS_OWNER_TYPE')

and (t3.person_type=t7.value_cd and t7.item_cd='PERSON_TYPE');

二、第二种：

set @rownum=0;

select @rownum:=@rownum+1 as rownum, t.* from table_name t order by t.id desc limit 10

(1)简单实例

set @rownum=0;

select @rownum:=@rownum+1 as rownum, t.hs_id from house_info_20170803 t order by t.hs_id desc limit 10