viewdemo6.sql

来自「T-SQL示例大全」· SQL 代码 · 共 22 行

/*
文件名称: ViewDemo6.sql
目        的: 示范如何自定义视图的各个字段的名称
*/
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