创建与修改视图-优快云博客

本文介绍了一个名为 Mobile_Att_VesselMachine 的视图的创建及后续修改过程。该视图通过连接多个表来整合 VM_Assets_Allocation 和 VM_Assets_Scrap 表中关于资产分配和报废工作的附件信息，并对文件路径进行了格式调整。

创建视图：

create view Mobile_Att_VesselMachine
as
SELECT b.WorkID AS WorkID, '附件' AS AttachmentType, att.[FileName] AS AttachmentName,
replace(RIGHT(att.FullPath, (LEN(att.FullPath) - CHARINDEX('\doc\', att.FullPath)) - 4), '\', '/') AS FullPath,
att.FileSize AS FileSize, 'Contract' AS OpeCode
FROM VM_Assets_Allocation b INNER JOIN
Shj_Sys_AttachmentMapping attmapping ON attmapping.ResourceID = b.ID AND attmapping.Status != - 1 JOIN
Shj_Sys_Attachment att ON att.ID = attmapping.AttachmentID AND att.Status != - 1
WHERE b.Status != - 1 AND b.WorkID > 0

修改视图：

ALTER view Mobile_Att_VesselMachine
as
SELECT b.WorkID AS WorkID, '附件' AS AttachmentType, att.[FileName] AS AttachmentName,
replace(RIGHT(att.FullPath, (LEN(att.FullPath) - CHARINDEX('\doc\', att.FullPath)) - 4), '\', '/') AS FullPath,
att.FileSize AS FileSize, 'Contract' AS OpeCode
FROM VM_Assets_Allocation b INNER JOIN
Shj_Sys_AttachmentMapping attmapping ON attmapping.ResourceID = b.ID AND attmapping.Status != - 1 JOIN
Shj_Sys_Attachment att ON att.ID = attmapping.AttachmentID AND att.Status != - 1
WHERE b.Status != - 1 AND b.WorkID > 0
union

SELECT b.WorkID AS WorkID, '附件' AS AttachmentType, att.[FileName] AS AttachmentName,
replace(RIGHT(att.FullPath, (LEN(att.FullPath) - CHARINDEX('\doc\', att.FullPath)) - 4), '\', '/') AS FullPath,
att.FileSize AS FileSize, 'Contract' AS OpeCode
FROM VM_Assets_Scrap b INNER JOIN
Shj_Sys_AttachmentMapping attmapping ON attmapping.ResourceID = b.ID AND attmapping.Status != - 1 JOIN
Shj_Sys_Attachment att ON att.ID = attmapping.AttachmentID AND att.Status != - 1
WHERE b.Status != - 1 AND b.WorkID > 0