One: the first kind:
Select (@i:[email protected]+1) as I, table_name.* from table_name, (select @i:=0) as it
For example:
(1) Simple example: Select (@i:[email protected]+1) i,user_id from spcp_user_20170803, (select @i:=0) as it ORDER by T.user_i D desc;
(2) Complex example:
SELECT
(@i:[email protected]+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 ');
Ii. Second Type:
Set @rownum = 0;
Select @rownum: [Email protected]+1 as rownum, t.* from table_name t ORDER BY t.id DESC LIMIT 10
(1) Simple example
Set @rownum = 0;
Select @rownum: [Email protected]+1 as rownum, t.hs_id from house_info_20170803 t order by t.hs_id DESC LIMIT 10
Add an ordinal column to the results of a query in MySQL