view.sql

来自「foxpro连接sqlserver的例子」· SQL 代码 · 共 125 行

-- 商品销售发票
-- 表.视图
-- 红虎于2002.12.14

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_InvoiceDetail]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_InvoiceDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_InvoiceList]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_InvoiceList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_Order]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_Order]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_OutItemCount]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_OutItemCount]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.view_InvoiceDetail    Script Date: 02-12-4 11:27:26 *****
 With Encryption*/
CREATE VIEW dbo.view_InvoiceDetail
AS
SELECT dbo.view_Order.InvoiceNo, dbo.view_Order.InvoiceDate, dbo.CustMast.CustName, 
      dbo.CustMast.CustAddress, dbo.CustMast.CustTelephone, dbo.view_Order.ItemNo, 
      dbo.view_Order.ItemFullName, dbo.view_Order.ItemModel, dbo.view_Order.Notes, 
      dbo.Users.UserName, dbo.view_Order.ItemUnit, dbo.view_Order.ItemPrice, 
      dbo.view_Order.ItemFrom, dbo.view_Order.InvoiceAmount, 
      dbo.view_Order.InvoiceQty, dbo.view_Order.InvoicePrice, 
      dbo.view_Order.UserID
FROM dbo.view_Order LEFT OUTER JOIN
      dbo.Users ON dbo.view_Order.UserID = dbo.Users.ID LEFT OUTER JOIN
      dbo.CustMast ON dbo.view_Order.CustID = dbo.CustMast.ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.view_InvoiceList    Script Date: 02-12-4 11:27:26 *****
 With Encryption*/
CREATE VIEW dbo.view_InvoiceList
AS
SELECT dbo.CustMast.CustName, dbo.Users.UserName, dbo.InvoiceOrder.InvoiceNo, 
      dbo.InvoiceOrder.InvoiceDate, dbo.InvoiceOrder.ID, 
      AVG(dbo.InvoiceItemList.InvoicePrice) AS InvoicePrice, 
      SUM(dbo.InvoiceItemList.InvoiceQty) AS InvoiceQty, 
      SUM(dbo.InvoiceItemList.InvoiceAmount) AS InvoiceAmount
FROM dbo.InvoiceOrder INNER JOIN
      dbo.InvoiceItemList ON 
      dbo.InvoiceOrder.ID = dbo.InvoiceItemList.InvoiceID LEFT OUTER JOIN
      dbo.Users ON dbo.InvoiceOrder.UserID = dbo.Users.ID LEFT OUTER JOIN
      dbo.CustMast ON dbo.InvoiceOrder.CustID = dbo.CustMast.ID
GROUP BY dbo.Users.UserName, dbo.InvoiceOrder.InvoiceNo, 
      dbo.InvoiceOrder.InvoiceDate, dbo.InvoiceOrder.ID, dbo.CustMast.CustName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.view_Order    Script Date: 02-12-4 11:27:26 *****
 With Encryption*/
CREATE VIEW dbo.view_Order
AS
SELECT dbo.InvoiceOrder.ID, dbo.InvoiceOrder.InvoiceNo, dbo.InvoiceOrder.InvoiceDate, 
      dbo.InvoiceOrder.CustID, dbo.InvoiceItemList.ID AS ItemListID, 
      dbo.InvoiceItemList.ItemID, dbo.ItemMast.ItemNo, dbo.ItemMast.ItemFullName, 
      dbo.ItemMast.ItemModel, dbo.InvoiceOrder.Notes, dbo.InvoiceOrder.UserID, 
      dbo.InvoiceItemList.InvoicePrice, dbo.InvoiceItemList.InvoiceQty, 
      dbo.InvoiceItemList.InvoiceAmount, dbo.ItemMast.ItemFrom, dbo.ItemMast.ItemPrice, 
      dbo.ItemMast.ItemUnit
FROM dbo.ItemMast RIGHT OUTER JOIN
      dbo.InvoiceItemList ON 
      dbo.ItemMast.ID = dbo.InvoiceItemList.ItemID RIGHT OUTER JOIN
      dbo.InvoiceOrder ON dbo.InvoiceItemList.InvoiceID = dbo.InvoiceOrder.ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.OutItemCount_view
AS
SELECT AVG(ISNULL(dbo.InvoiceItemList.InvoicePrice, 0)) AS AvgPrice, 
      SUM(ISNULL(dbo.InvoiceItemList.InvoiceQty, 0)) AS SumQty, 
      SUM(ISNULL(dbo.InvoiceItemList.InvoiceAmount, 0)) AS SumAmount, 
      dbo.ItemMast.ItemNo, dbo.ItemMast.ItemFullName, dbo.ItemMast.ItemModel, 
      dbo.ItemMast.ItemFrom, dbo.ItemMast.ItemUnit, dbo.ItemMast.ItemPrice
FROM dbo.InvoiceItemList RIGHT OUTER JOIN
      dbo.ItemMast ON dbo.InvoiceItemList.ItemID = dbo.ItemMast.ID
GROUP BY dbo.ItemMast.ItemNo, dbo.ItemMast.ItemFullName, dbo.ItemMast.ItemModel, 
      dbo.ItemMast.ItemFrom, dbo.ItemMast.ItemUnit, dbo.ItemMast.ItemPrice

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO