摘要:本文主要向大家介绍了MySQL数据库之在mysql中给查询的结果添加序号列 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库之在mysql中给查询的结果添加序号列 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
一:第一种:
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
技术分享
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号