可以添加列(PK)以查看。如果是..我们可以吗?
这是我的观点:
CREATE VIEW [dbo].[SalesDetailView]
AS
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
FROM
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'
您可以使用row_number
添加唯一列,例如:
CREATE VIEW [dbo].[SalesDetailView]
AS
SELECT
row_number() over (order by SH.CreatedDateTime) as PK,
DATENAME(yyyy, SH.CreatedDateTime) AS Year,
如果这不是您的意思,请澄清您的问题。一些示例结果总是有帮助的。
当您看到视图的sp_help时,提到的身份列不过是基础表的身份。
如果您已经在基础表中添加了"身份"列,则只需要更改视图并在选择STMT中添加身份列。
否则您需要先将身份添加到表格中,然后编辑视图以在选择STMT中添加列。
ALTER VIEW [dbo].[SalesDetailView]
AS
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
FROM
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'