create table Employees (
EmployeeID char(6) primary key not null,
Name varchar(6) not null,
Education varchar(4) not null,
Birthday datetime not null,
Sex bit not null default 1,
WorkYear tinyint null,
Address varchar(40),
PhoneNumber char(12) null,
Pepartment char(3) not null
)
insert into Employees values('000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668',2);
insert into Employees values('010008','伍容华','本科','1976-03-28',1,3,'北京东路100-2','83321321',1);
insert into Employees values('020010','王向容','硕士','1982-12-09',1,2,'四牌楼10-0-108','83792361',1);
insert into Employees values('020018','李丽','大专','1960-07-30',0,6,'中山东路102-2','83413301',1);
insert into Employees values('102201','刘明','本科','1972-10-18',1,3,'虎距路100-2','83606608',5);
insert into Employees values('102208','朱骏','硕士','1965-09-28',1,2,'牌楼巷5-3-106','84708817',5);
insert into Employees values('108991','钟敏','硕士','1979-08-10',0,4,'中山路10-3-105','83346722',3);
insert into Employees values('111006','张石兵','本科','1974-10-01',1,1,'解放路34-1-203','84563418',5);
insert into Employees values('210678','林涛','大专','1977-04-02',1,2,'中山北路24-35','83467336',3);
insert into Employees values('302566','李玉珉','本科','1968-09-20',1,3,'热和路209-3','58765991',4);
insert into Employees values('308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','83308901',4);
insert into Employees values('504209','陈林琳','大专','1969-09-03',0,5,'汉中路120-4-12','84468158',4);
select EmployeeID,Name,Address,Pepartment=
case Pepartment
when 1 then '财务部'
when 2 then '人力资源部'
when 3 then '经理办公室'
when 4 then '研发部'
when 5 then '市场部'
end
from Employees
--(3) 使用if 语句实现以上功能
------解决方案--------------------
不想用，也可用 Pepartment=replace(replace(replace(replace(replace(rtrim(Pepartment),'1','财务部'),'2','人力资源部'),'3','经理办公室'),'4','研发部'),'5','市场部')
------解决方案--------------------
- SQL code
create table Employees ( EmployeeID char(6) primary key not null, Name varchar(6) not null, Education varchar(4) not null, Birthday datetime not null, Sex bit not null default 1, WorkYear tinyint null, Address varchar(40), PhoneNumber char(12) null, Pepartment char(3) not null )insert into Employees values('000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668',2);insert into Employees values('010008','伍容华','本科','1976-03-28',1,3,'北京东路100-2','83321321',1);insert into Employees values('020010','王向容','硕士','1982-12-09',1,2,'四牌楼10-0-108','83792361',1);insert into Employees values('020018','李丽','大专','1960-07-30',0,6,'中山东路102-2','83413301',1);insert into Employees values('102201','刘明','本科','1972-10-18',1,3,'虎距路100-2','83606608',5);insert into Employees values('102208','朱骏','硕士','1965-09-28',1,2,'牌楼巷5-3-106','84708817',5);insert into Employees values('108991','钟敏','硕士','1979-08-10',0,4,'中山路10-3-105','83346722',3);insert into Employees values('111006','张石兵','本科','1974-10-01',1,1,'解放路34-1-203','84563418',5);insert into Employees values('210678','林涛','大专','1977-04-02',1,2,'中山北路24-35','83467336',3);insert into Employees values('302566','李玉珉','本科','1968-09-20',1,3,'热和路209-3','58765991',4);insert into Employees values('308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','83308901',4);insert into Employees values('504209','陈林琳','大专','1969-09-03',0,5,'汉中路120-4-12','84468158',4);gocreate function useif(@p char(3))returns nvarchar(10)asbegindeclare @s nvarchar(10)if @p=1 set @s='财务部'else if @p=2 set @s='人力资源部'else if @p=3 set @s='经理办公室'else if @p=4 set @s='研发部'else if @p=5 set @s='市场部'return @sendgoselect EmployeeID,Name,Address,Pepartment=dbo.useif(Pepartment)from Employees/*EmployeeID Name Address Pepartment---------- ------ ---------------------------------------- ----------000001 王林 中山路32-1-508 人力资源部010008 伍容华 北京东路100-2 财务部020010 王向容 四牌楼10-0-108 财务部020018 李丽 中山东路102-2 财务部102201 刘明 虎距路100-2 市场部102208 朱骏 牌楼巷5-3-106 市场部108991 钟敏 中山路10-3-105 经理办公室111006 张石兵 解放路34-1-203 市场部210678 林涛 中山北路24-35 经理办公室302566 李玉珉 热和路209-3 研发部308759 叶凡 北京西路3-7-52 研发部504209 陈林琳 汉中路120-4-12 研发部(12 行受影响)*/godrop table Employeesdrop function dbo.useif