proceduredemo23.sql

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

/* 文件名称: ProcedureDemo23.sql */
USE 北风贸易
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SalaryOverview') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.SalaryOverview
GO

CREATE PROCEDURE dbo.SalaryOverview
AS
SELECT 部门,
        性别 = CASE 员工性别
               WHEN 0 THEN '女'
               WHEN 1 THEN '男'
             END,
       婚姻状况 = CASE 婚姻状况
                   WHEN 0 THEN '未婚'
                   WHEN 1 THEN '已婚'
                 END,          
       最低薪资 = MIN(目前薪资),
       最高薪资 = MAX(目前薪资),
       平均薪资 = AVG(目前薪资)
  FROM 飞狐工作室
  GROUP BY 部门,
           CASE 员工性别
             WHEN 0 THEN '女'
             WHEN 1 THEN '男'
           END,
           CASE 婚姻状况
             WHEN 0 THEN '未婚'
             WHEN 1 THEN '已婚'
           END
  ORDER BY 部门

GO