mysql高低排序_MySQL查询行号、多表关联更新、字段排序忽略字符按数字大小排列...

1、查询行号

SELECT @rownum:=@rownum+1 AS rownum , t.*  FROM (SELECT @rownum:=0) r,`buss_house_info` t

WHERE t.community_no='0000000103'

2、多表关联更新(本例表中新增了一个字段更新为行号)

update buss_house_info b,

(

SELECT @rownum:=@rownum+1 AS rownum, t.unit_no+0,t.room_num+0,t.*  FROM (SELECT @rownum:=0) r,`buss_house_info` t where t.community_no='0000000363'

order by t.unit_no+0, t.room_num+0

) tt

set b.room_num_sort=tt.rownum ,b.unit_no_sort=tt.rownum

where b.house_no=tt.house_no

3、字段排序忽略字符按数字大小排列，排序字段+0即可

SELECT  t.unit_no+0,t.room_num+0,t.*  FROM `buss_house_info` t

WHERE t.community_no='0000000363' ORDER BY t.unit_no+0, t.room_num+0;