本文主要向大家介绍了MySQL数据库之在mysql中给查询的结果添加序号列 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
一:第一种:
select(@i:=@i+1)asi,table_name.*fromtable_name,(select@i:=0)asit
例如:
(1)简单实例:select(@i:=@i+1)i,user_idfromspcp_user_0803,(select@i:=0)asitORDERBYt.user_iddesc;
技术分享
(2)复杂实例:
SELECT
(@i:=@i+1)asi,hs_cd,hs_owner_persons,hs_owner_certy_num,hs_full_addr,t5.value_nameashs_type,t6.value_nameashs_owner_type,hs_use_size,hs_build_size,t2.cj_num,t2.db_num,t2.high_age_num,t2.ls_person_num,t7.value_nameasperson_type,_name
FROM
house_info_0803t1,house_person_info_0803t2,person_info_0803t3,cmp_orgt4,cfg_valuet5,cfg_valuet6,cfg_valuet7,(select@i:=0)asit
WHERE
t1.hs_owner_certy_numIN(
SELECT
hs_owner_certy_num
FROM
house_info_0803
GROUPBY
hs_owner_certy_num
HAVING
COUNT(*)>1
)
andt1.hs_id=t2.hs_id
andt2.hs_id=t3.old_hs_id
andt1.tt_org_id=_id
and(t1.hs_type=t5.value_cdandt5.item_cd=‘HS_TYPE‘)
and(t1.hs_owner_type=t6.value_cdandt6.item_cd=‘HS_OWNER_TYPE‘)
and(t3.person_type=t7.value_cdandt7.item_cd=‘PERSON_TYPE‘);
二、第二种:
set@rownum=0;
select@rownum:=@rownum+1asrownum,t.*fromtable_nametorderbyt.iddesclimit10
(1)简单实例
set@rownum=0;
select@rownum:=@rownum+1asrownum,t.hs_idfromhouse_info_0803torderbyt.hs_iddesclimit10
技术分享
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!