viewdemo7.sql

T-SQL示例大全

/*
文件名称: ViewDemo7.sql
目        的: 示范也可于 SELECT 命令中自定义视图的各个字段的名称
*/
USE 北风贸易
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.vw_销售业绩最佳的前三名业务员') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.vw_销售业绩最佳的前三名业务员
GO

CREATE VIEW vw_销售业绩最佳的前三名业务员
AS
SELECT TOP 3
              a.员工编号,
              a.姓名,
             销售总金额 = SUM(c.单价 * c.数量*(1 - c.折扣))
  FROM 员工 a INNER JOIN 订货主档 b ON a.员工编号 = b.员工编号
                         INNER JOIN 订货明细 c ON b.订单号码 = c.订单号码
  GROUP BY a.员工编号,a.姓名
  ORDER BY 3 DESC

GO