精选 SQL 脚本

//----- 自动设置商品编号

SELECT ID, right( '100000'+ row_number()OVER(ORDER BY ID) ,6 ) AS maxBHao FROM Cloud_ChpInfo
ORDER BY ID

 

 --- 字段字符型的数字，按数字排序 如果字符串，也可以数字排序

SELECT * FROM xxxx
ORDER BY RIGHT(REPLICATE('0',10) + CAST(字段名 AS varchar(10)),10) ASC

sql自关联查询上级id对应的名字

    方法一：关联查询

select ci1.id,(select ci2.name from city_info ci2 where ci2.id = ci1.pId) as pName,ci1.name,ci1.title,ci1.type from city_info ci1;

 

    方法二：左外连接查询

select ci1.id,ci2.name pName,ci1.name,ci1.title,ci1.type from city_info ci1 LEFT JOIN city_info ci2 ON ci1.pId = ci2.id;

   查询结果