sample152.sql

T-SQL示例大全

/* 文件名称: Sample152.sql */
EXEC sp_dboption '北风贸易','select into/bulkcopy','TRUE'
USE 北风贸易

SELECT a.员工编号,a.姓名,SUM(c.数量) AS 销售总数 
  INTO aMount
  FROM 员工 a INNER JOIN 订货主档 b ON a.员工编号 = b.员工编号
                         INNER JOIN 订货明细 c ON b.订单号码 = c.订单号码
  GROUP BY a.员工编号,a.姓名

SELECT a.员工编号,
       a.姓名,
       a.销售总数,
       COUNT(b.员工编号) AS 业绩比此人好的业务员的人数,
       AVG(b.销售总数) AS 业绩比此人好的业务员的平均销售总数
  FROM aMount a, aMount b
  WHERE a.销售总数 < b.销售总数
  GROUP BY a.员工编号,a.姓名,a.销售总数

EXEC sp_dboption '北风贸易','select into/bulkcopy','FALSE'
DROP TABLE aMount