在sql server中创建视图“每个视图或函数中的名称

问题描述

我正在尝试在 sql server 2012 中创建视图，但出现此错误消息

I'm trying to create view in sql server 2012, but I have this error message

Column names in each view or function must be unique. Column name 'Lastname' in view or function 'vFileImportDPERManagers' is specified more than once.

这是我的视图代码:

    GO



CREATE VIEW [dbo].[vFileImportDPERManagers]
    AS
SELECT M.*,
Managers.*

FROM            dbo.vFileImportDPERDebtors M WITH (NOLOCK)
CROSS APPLY 
(
    SELECT
        M1.Man.value('@j11','varchar(20)') as ManagerKey,
        M1.Man.value('@j12','varchar(50)') as Lastname,
        M1.Man.value('@j13','varchar(50)') as Firstname,
        M1.Man.value('@j14','numeric') as ManagerFunctionId


    FROM DebtorXml.nodes('d1/d36/t324/j1') as M1(Man)
) as Managers
GO

推荐答案

在您的情况下，表 dbo.vFileImportDPERDebtors 将有一个名为Lastname"的列.如果您按名称选择所有列(即，将 * 扩展为名称)，您可以为一个或两个姓氏列设置别名，SELECT M.Lastname as Lastname1, Managers.Lastname as Lastname2....通常认为最好的做法是不使用SELECT *".

In your case the table dbo.vFileImportDPERDebtors will have a column called 'Lastname'. If you select all of your columns by name (i.e. expand * into names) you can alias one or both of the Lastname columns, SELECT M.Lastname as Lastname1, Managers.Lastname as Lastname2.... It's generally considered best-practice not to use 'SELECT *'.

这篇关于在sql server中创建视图“每个视图或函数中的名称必须是唯一的"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！