SQLSERVER常用函数使用案例
需求:多附件上传根据名称排序后自动命名(规则:附件01_【文件名】,附件02_【文件名】。。。附件10_文件名】依次类推)
数据
SELECT
ATTACH_ID attachId,
'附件' + sortNo + '_' + name name,
ATTACH_SIGN_CD bizKey,
SIZE size,
URI uri,
UPLOAD_TIME uploadTime,
UPLOADER uploader
FROM
(
SELECT
a.ATTACH_ID,
a.ATTACH_SIGN_CD,
a.NAME,
SIZE,
a.URI,
a.UPLOAD_TIME,
a.UPLOADER,
CASE
WHEN len(a.sortNo) = 1 THEN
'0' + sortNo
ELSE
sortNo
END sortNo
FROM
(
SELECT
ATTACH_ID,
ATTACH_SIGN_CD,
NAME,
SIZE,
URI,
UPLOAD_TIME,
UPLOADER,
CAST (
ROW_NUMBER () OVER (ORDER BY name DESC) AS VARCHAR (20)
) sortNo
FROM
IOP_ATTACH
) a
) f