sqlserver存储过程的纠结问题
create database YGGL
use YGGL
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 * from Employees
create table Departments(
DepartmentID int not null primary key identity(1,1),
DepartmentName char(20) not null,
Note varchar(100) null
)
insert into Departments values(1,'财务部',null);
insert into Departments values(2,'人力资源部',null);
insert into Departments values (3,'经理办公室',null);
insert into Departments values(4,'研发部',null);
insert into Departments values(5,'市场部',null);
create table Salary(
EmployeeID char(6) primary key not null,
InCome float not null,
OutCome float not null,
)
insert into Salary values('000001',2100.8,123.09)
insert into Salary values('010008',1582.62,88.03);
insert into Salary values('102201',2569.88,185.65);
insert into Salary values('111006',1987.01,79.58);
insert into Salary values('504209',2066.15,108.0);
insert into Salary values('302566',2980.7,210.2);
insert into Salary values('108991',3259.98,281.52);
insert into Salary values('020010',2860.0,198.0);
insert into Salary values('020018',2347.68,180.0);
insert into Salary values('308759',2531.98,199.08);
insert into Salary values('210678',2240.0,121.0);
insert into Salary values('102208',1980.0,100.0);
--(a)创建存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作
[最优解释]
Create proc Auto_Pepartment
as
update a
set Pepartment=b.DepartmentID
from Employees as a
inner join Departments as b on b.DepartmentName='经理办公室' and a.Pepartment<>b.DepartmentID
where a.WorkYear>6
[其他解释]
update Employees
set Pepartment = n.DepartmentID
from Employees m, Departments n
where m.WorkYear >= 6 and n.DepartmentName = '经理办公室'
select * from Employees
/*
EmployeeID Name Education Birthday Sex WorkYear Address PhoneNumber Pepartment
---------- ------ --------- ------------------------------------------------------ ---- -------- ---------------------------------------- ------------ ----------
000001 王林 大专 1966-01-23 00:00:00.000 1 8 中山路32-1-508 83355668 3
010008 伍容华 本科 1976-03-28 00:00:00.000 1 3 北京东路100-2 83321321 1
020010 王向容 硕士 1982-12-09 00:00:00.000 1 2 四牌楼10-0-108 83792361 1
020018 李丽 大专 1960-07-30 00:00:00.000 0 6 中山东路102-2 83413301 3
102201 刘明 本科 1972-10-18 00:00:00.000 1 3 虎距路100-2 83606608 5
102208 朱骏 硕士 1965-09-28 00:00:00.000 1 2 牌楼巷5-3-106 84708817 5
108991 钟敏 硕士 1979-08-10 00:00:00.000 0 4 中山路10-3-105 83346722 3
111006 张石兵 本科 1974-10-01 00:00:00.000 1 1 解放路34-1-203 84563418 5
210678 林涛 大专 1977-04-02 00:00:00.000 1 2 中山北路24-35 83467336 3
302566 李玉珉 本科 1968-09-20 00:00:00.000 1 3 热和路209-3 58765991 4
308759 叶凡 本科 1978-11-18 00:00:00.000 1 2 北京西路3-7-52 83308901 4
504209 陈林琳 大专 1969-09-03 00:00:00.000 0 5 汉中路120-4-12 84468158 4
（所影响的行数为 12 行）
*/
[其他解释]
这个需求有点问题.
不是所有工作六年的人都会到经理办公室工作.
[其他解释]
楼主是否条件没有贴完整？
只是更新>6年员工的部门用1楼方法就行了。
[其他解释]
嗯 是呀 只要工作六年的就可以到经理办公室工作了，条件是这样的，一直在外面，没来得及回复 不好意思 晚上回去再看 谢谢各位了
[其他解释]
还有啊 --创建存储过程,使用游标计算本科及以上学历的员工在总员工数中占的比例
完了就该结贴了 3q
[其他解释]
本帖最后由 roy_88 于 2011-11-16 23:11:23 编辑
Create proc Auto_Pepartment
as
select a.*
from Employees as a
inner join Departments as b on b.DepartmentName='经理办公室' and a.Pepartment<>b.DepartmentID
where a.WorkYear>=6
go
Create proc Auto_Pepartment2
as
select a.*
from Employees as a
inner join Departments as b on b.DepartmentName='经理办公室' and a.Pepartment<>b.DepartmentID
where a.WorkYear>6
go
Create proc Auto_Pepartment
as
select str(sum(case when Education not in(N'大专',N'高中',N'中专') then 1 else 0 end)*100.0/count(1),5,2) as 本科比例
from Employees
go
Education not in(N'大专') --加入不计算的学历，如高中
[其他解释]
大侠呀 计算比例那里运行无法通过呀 说是Education not in(N'大专',N'高中not附近有语法错误
[其他解释]
如果条件就是大于6年，那用1楼的就可以实现
[其他解释]
呵呵 几个月之前，我也是分给小弟这样的一个任务，让他去写一个游标，写一个存储过程，计算出公司的每月成本费用……呵呵，他也纠结了几天………希望大家都变成高手，我也多多学习……谢谢大家……
[其他解释]
创建存储过程，使用游标计算本科及以上学历的员工在总员工中所占的比例
怎么搞啊
[其他解释]
create procedure p_Bili @b_l float output,@Z_y char(4)
AS
BEGIN
declare @counts int
declare @num int
declare @X_EM_ID char(6)
declare @E_U char(4)
declare @sum1 int
set @sum1=0
set @counts=(select count(*) from Employees)
SET @num=(select count(*) from Employees)
declare C_education cursor for
select EmployeeID,education from Employees
open C_education
fetch C_education into @X_EM_ID,@E_U
while @counts>0
begin
set @counts=@counts-1
IF @E_U!=@Z_y
set @sum1=@sum1+1
fetch C_education into @X_EM_ID,@E_U
end
set @b_l= @sum1*1.0/@num
close C_education
deallocate C_education
END
declare @b_2 float
declare @Z_y char(4)
set @Z_y='大专'
exec p_Bili @b_2 output,@Z_y
select @b_2 as '本科以上比例'
drop procedure p_Bili