如何在视图中添加一个新列（作为主键列）？

0 人关注

是否可以在视图中添加列(PK)。如果可以，我们该如何做？

这是我的观点。

CREATE VIEW [dbo].[SalesDetailView]
SELECT
    DATENAME(yyyy, SH.CreatedDateTime) AS Year,
    DATENAME(mm, SH.CreatedDateTime) AS Month, SH.CreatedDateTime AS Date,
    SH.TransactionName AS Type, SH.SalesHeaderID AS No,
    Customer.CustomerName AS Customer,
    CustomerGroup.CustomerGroupName AS Customer_Group, SH.Reference AS Memo,
    Item.ItemName AS Item, SD.LineDescription AS Item_Description,
    Item.ItemType AS Item_Type, Item.UOM,
    ItemGroup.ItemGroupName AS Item_Group,
    CAST (SD.Quantity AS INT) AS Quantity, CAST(SD.Amount AS MONEY) AS Amount,
    SD.Price, SD.Discount, SH.ExchangeRate AS Exchange_Rate,
    Currency.CurrencyDescription AS Currency, SD.ClassID AS Class_ID,
    SD.SalesTaxID AS SalesTax_ID, SalesTaxGroup.SalesTaxGroupName AS Tax_Group,
    Employee.EmployeeName AS Salesperson,
    ShippingMethod.ShippingMethodName AS Shipping_Method,
    PaymentTerm.PaymentTermName AS Payment_Term,
    PaymentMethod.PaymentMethodName AS Payment_Method
    SalesHeader SH, Customer
LEFT OUTER JOIN 
    SalesDetail SD ON SH.SalesHeaderID = SD.SalesHeaderID
LEFT OUTER JOIN 
    Item ON SD.ItemID = Item.ItemID
LEFT OUTER JOIN 
    ItemGroup ON Item.ItemGroupId = ItemGroup.ItemGroupID
LEFT OUTER JOIN 
    CustomerGroup ON Customer.CustomerGroupId = CustomerGroup.CustomerGroupID
LEFT OUTER JOIN 
    Employee ON Customer.EmployeeID = Employee.EmployeeID
LEFT OUTER JOIN 
    Currency ON Customer.CurrencyID = Currency.CurrencyID
LEFT OUTER JOIN 
    SalesTaxGroup ON Customer.SalesTaxGroupID = SalesTaxGroup.SalesTaxGroupID
LEFT OUTER JOIN 
    PaymentTerm ON Customer.PaymentTermID = PaymentTerm.PaymentTermID
LEFT OUTER JOIN 
    ShippingMethod ON Customer.ShippingMethodID = ShippingMethod.ShippingMethodID
LEFT OUTER JOIN 
    PaymentMethod ON Customer.PaymentMethodID = PaymentMethod.PaymentMethodID
WHERE
    SH.CustomerID = Customer.CustomerID
    AND SH.TransactionName <> 'SalesOrder'
AND Sh.TransactionName <> 'Quote'


           
            
             Johanna Larsson
            
            ：


            
             
              请将代码添加到你的帖子中，而不是链接到其他地方。


            
             
              对不起，该视图包含40行代码。我试图添加代码，但我不能。


            
             
              在这里发布了视图代码。一个评论：
              
               为什么
              
              
               
                Customer
               
              
              不能像其他所有的表一样正确地连接？使用
              
               
                INNER JOIN Customer ON  SH.CustomerID = Customer.CustomerID
               
              
              （并从
              
               
                WHERE
               
              
              子句中删除该条件），使之与所有其他表相同


            
             
              我在sql中得到这个错误：FROM子句中的对象 "Customer "和 "Customer "有相同的暴露名称。使用相关的名称来区分它们。


            
             
              如果你使用我的线与
              
               
                INNER JOIN Customer
               
              
              - 当然，你必须
              
               删除
              
              
               
                ..... , Customer
               
              
              从你的
              
               
                FROM
               
              
              语句中删除!


         
          sql-server


         
          sql-server-2008


         
          view


         
          primary-key


           
            
             你可以用
             
              
               row_number
              
             
             添加一个唯一列，比如。
            
            CREATE VIEW [dbo].[SalesDetailView]
SELECT
row_number() over (order by SH.CreatedDateTime) as PK,
    DATENAME(yyyy, SH.CreatedDateTime) AS Year,
如果这不是你的意思，请澄清你的问题。  一些例子的结果总是有帮助的。


            
             
              
               
                我在Employee表中添加了这一行：row_number() over (order by Employee.EmployeeID) as PK, 在Employee表中，EmployeeId列是身份列。  我运行了实体模型。现在，视图显示了正确的数据。问题是当我看到SQL中的视图设计时，它没有显示。当我从视图中移除row_number()时，它就显示在视图的设计中。


          
           
            
             
              
              
               Ruzbeh Irani
              
             
             
              发布于
              
              2012-10-28


           
            
             
              
               当你看到视图的sp_help时，所提到的身份列不过是底层表的身份。
              
              
               如果你已经在底层表中添加了身份列，你只需要改变你的视图并在选择语句中添加身份列。
              
              
               否则，你需要先在你的表中添加身份，然后编辑视图，在选择语句中添加该列。
              
              ALTER VIEW [dbo].[SalesDetailView]
SELECT
    DATENAME(yyyy, SH.CreatedDateTime) AS Year,
    DATENAME(mm, SH.CreatedDateTime) AS Month, SH.CreatedDateTime AS Date,
    SH.TransactionName AS Type, SH.SalesHeaderID AS No,
    Customer.CustomerName AS Customer,
    CustomerGroup.CustomerGroupName AS Customer_Group, SH.Reference AS Memo,
    Item.ItemName AS Item, SD.LineDescription AS Item_Description,
    Item.ItemType AS Item_Type, Item.UOM,
    ItemGroup.ItemGroupName AS Item_Group,
    CAST (SD.Quantity AS INT) AS Quantity, CAST(SD.Amount AS MONEY) AS Amount,
    SD.Price, SD.Discount, SH.ExchangeRate AS Exchange_Rate,
    Currency.CurrencyDescription AS Currency, SD.ClassID AS Class_ID,
    SD.SalesTaxID AS SalesTax_ID, SalesTaxGroup.SalesTaxGroupName AS Tax_Group,
    Employee.EmployeeName AS Salesperson,
    ShippingMethod.ShippingMethodName AS Shipping_Method,
    PaymentTerm.PaymentTermName AS Payment_Term,
    PaymentMethod.PaymentMethodName AS Payment_Method,
    [your column] as  PK
    SalesHeader SH, Customer
LEFT OUTER JOIN 
    SalesDetail SD ON SH.SalesHeaderID = SD.SalesHeaderID
LEFT OUTER JOIN 
    Item ON SD.ItemID = Item.ItemID
LEFT OUTER JOIN 
    ItemGroup ON Item.ItemGroupId = ItemGroup.ItemGroupID
LEFT OUTER JOIN 
    CustomerGroup ON Customer.CustomerGroupId = CustomerGroup.CustomerGroupID
LEFT OUTER JOIN 
    Employee ON Customer.EmployeeID = Employee.EmployeeID
LEFT OUTER JOIN 
    Currency ON Customer.CurrencyID = Currency.CurrencyID
LEFT OUTER JOIN 
    SalesTaxGroup ON Customer.SalesTaxGroupID = SalesTaxGroup.SalesTaxGroupID
LEFT OUTER JOIN 
    PaymentTerm ON Customer.PaymentTermID = PaymentTerm.PaymentTermID
LEFT OUTER JOIN 
    ShippingMethod ON Customer.ShippingMethodID = ShippingMethod.ShippingMethodID
LEFT OUTER JOIN 
    PaymentMethod ON Customer.PaymentMethodID = PaymentMethod.PaymentMethodID