求助，有一道题不会做了

[chengxuan104]

刚学sql数据库，这道题不知道哪出了错，找了半天，求各位大贤帮帮忙！！！ 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金＝年销售总额×提成率。
提成率规则如下：年销售总额5000元以下部分，提成率为10％，对于5000元及超过5000元部分，则提成率为15％。
create procedure nzj4 @nianfen char(10)
as
begin

declare @salerno char(8),@ordersum char(10),@jiangjin char(10)


declare nzj2 cursor for
select a.salerNo,sum(a.orderSum) 总销售额,YEAR(a.orderDate) 年份 --每个年度每个业务员的销售总额
from OrderMaster a,OrderMaster b
where a.salerNo=b.salerNo and convert(char(4), a.orderdate,120)=@nianfen --根据输入年份来查询
group by a.salerNo,YEAR(a.orderDate)
order by a.salerNo


open nzj2
create table #bonus( ---内存临时表,会话结束系统自动删除
salerno char(8),ordersum char(10),nianfen char(10),jiangjin char(10)
primary key(salerno) )



fetch nzj2 into @salerno ,@ordersum ,@nianfen
while (@@FETCH_STATUS=0)
begin
if @ordersum>5000
select @jiangjin=(@ordersum-5000)*0.15+5000*0.1
else select @jiangjin=@ordersum*0.1
select @salerno ,@ordersum ,@nianfen
insert into #bonus values( @salerno,@ordersum, @nianfen, @jiangjin)
fetch nzj1 into @salerno ,@ordersum ,@nianfen
end
close nzj2
deallocate nzj2
end


exec nzj4 2008

提示出错：消息 16915，级别 16，状态 1，过程 nzj4，第 9 行
名为 'nzj2' 的游标已存在。
消息 16905，级别 16，状态 1，过程 nzj4，第 16 行
游标已打开。
消息 245，级别 16，状态 1，过程 nzj4，第 26 行
在将 varchar 值 '19561.60 ' 转换成数据类型 int 时失败。

[chengxuan104]

附上表，这样更好试验。 set nocount on set dateformat ymd use master go if not exists(select * from syslogins where name='user01') exec sp_addlogin user01,888888 go /*create database*/ if exists(select *from sysdatabases where name='OrderDB') drop database OrderDB go create database OrderDB on primary (name='OrderDB1', filename='d:\OrderDB1.mdf', size=3, maxsize=5, filegrowth=1) log on (name=d20011812_log, filename='d:\OrderLog1.ldf', size=1, maxsize=5, filegrowth=1) go /*data mydatabase*/ use OrderDB go /* add my user*/ exec sp_adduser user01,user01 go ------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------- /*员工人事表*/ print'creat table Employee' go CREATE TABLE Employee( employeeNo char(8) not null /*员工编号*/ check(employeeNo like '[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), employeeName varchar(10) not null, /*员工姓名*/ sex char(1) not null, /*员工性别*/ birthday datetime null, /*员工生日*/ address varchar(50) null, /*员工住址*/ telephone varchar(20) null, /*员工电话*/ hireDate datetime not null, /*雇佣日期*/ department varchar(30) not null, /*所属部门*/ headShip varchar(10) not null, /*职务*/ salary numeric(8,2) not null, /*薪水*/ constraint EmployeePK primary key (employeeNo) ) go go /*人事表（employee）数据：*/ insert Employee values('E2005001','喻自强','M','19650415','南京市青海路18号', '13817605008', '20050206','财务科','科长',5800.80) insert Employee values('E2005002','张小梅','F','19731101','上海市北京路8号', '13607405016', '20050328','业务科','职员',2400) insert Employee values('E2005003','张小娟','F','19730306','上海市南京路66号', '13707305025', '20050328','业务科','职员',2600) insert Employee values('E2005004','张露', 'F','19670105','南昌市八一大道130号', '15907205134', '20050328','业务科','科长',4100) insert Employee values('E2005005','张小东','M','19730903','南昌市阳明路99号', '15607105243', '20050328','业务科','职员',1800) insert Employee values('E2006001','陈辉', 'M','19651101','南昌市青山路100号', '13607705352', '20060328','办公室','主任',4000) insert Employee values('E2006002','韩梅', 'F','19731211','上海市浦东大道6号', '13807805461', '20061128','业务科','职员',2600) insert Employee values('E2006003','刘风', 'F','19730521','江西财经大学5栋1-101室', '15907805578', '20060228','业务科','职员',2500) insert Employee values('E2007001','吴浮萍','M','19730912','南昌高新开发区12号', null, '20070628','业务科','职员',2500) insert Employee values('E2007002','高代鹏','M','19730102','南昌高新开发区56号', null, '20071128','办公室','文员',2000) insert Employee values('E2008001','陈诗杰','M','19680106','江西财经大学12栋3-304室', null, '20081206','财务科','出纳',3200) insert Employee values('E2008002','张良', 'M','19720216','上海市福州路135号', null, '20080228','业务科','职员',2700) insert Employee values('E2008003','黄梅莹','F','19720515','上海市九江路88号', null, '20080228','业务科','职员',3100) insert Employee values('E2008004','李虹冰','F','19721013','南昌市中山路1号', null, '20080528','业务科','职员',3400) insert Employee values('E2008005','张小梅','F','19701106','深圳市阳关大道10号', null, '20081118','财务科','会计',5000) go ------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------- /*客户表*/ print 'create Customer' go create table Customer( customerNo char(9) not null primary key,/*客户号*/ check(customerNo like '[C][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), customerName varchar(40) not null, /*客户名称*/ telephone varchar(20) not null, /*客户电话*/ address char(40) not null, /*客户住址*/ zip char(6) null /*邮政编码*/ ) go /*客户表（customer）数据：*/ insert Customer values('C20050001','统一股份有限公司', '022-3566021', '天津市', '220012') insert Customer values('C20050002','兴隆股份有限公司', '022-3562452', '天津市', '220301') insert Customer values('C20050003','上海生物研究室', '010-2121000', '北京市', '108001') insert Customer values('C20050004','五一商厦', '021-4532187', '上海市', '210100') insert Customer values('C20060001','大地商城', '010-1165152', '北京市', '100803') insert Customer values('C20060002','联合股份有限公司', '021-4568451', '上海市', '210100') insert Customer values('C20070001','南昌市电脑研制中心','0791-4412152', '南昌市', '330046') insert Customer values('C20070002','世界技术开发公司', '021-4564512', '上海市', '210230') insert Customer values('C20070003','万事达股份有限公司','022-4533141', '天津市', '220400') insert Customer values('C20080001','红度股份有限公司', '010-5421585', '北京市', '100800') go ------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------- /*产品名称表*/ print 'create Product' go create table Product( productNo char(9) not null primary key, /*商品编号*/ productName varchar(40) not null, /*商品名称*/ productClass varchar(20) not null, /*商品类别*/ productPrice numeric(7,2) not null, /*商品定价*/ ) go go /*产品名称表（product）数据：*/ insert Product values('P20050001','32M DRAM', '内存',80.70) insert Product values('P20050002','17寸显示器', '显示器',700.00) insert Product values('P20050003','120GB硬盘', '存储器',300.00) insert Product values('P20050004','3.5寸软驱', '设备',35.00) insert Product values('P20050005','键盘', '设备',100.60) insert Product values('P20060001','VGA显示卡', '显示器',200.60) insert Product values('P20060002','网卡', '设备',66.00) insert Product values('P20060003','Pentium100CPU', '处理器',1200.00) insert Product values('P20070001','1G DDR', '内存',256.00) insert Product values('P20070002','52倍速光驱', '设备',200.00) insert Product values('P20070003','计算机字典', '图书',100.00) insert Product values('P20070004','9600bits/s调制解调', '设备',320.00) insert Product values('P20080001','Pentium主板', '主板',890.00) insert Product values('P20080002','硕泰克SL—K8AN-RL主板','主板',1100.00) insert Product values('P20080003','龙基777FT纯平显示器', '显示器',900.00) go ------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------- /*订单主表*/ print 'create OrderMaster' go create table OrderMaster( orderNo char(12) not null primary key,/*订单编号*/ customerNo char(9) not null, /*客户号*/ salerNo char(8) not null, /*业务员编号*/ orderDate datetime not null, /*订货日期*/ orderSum numeric(9,2) not null, /*订单金额*/ invoiceNo char(10) not null, /*发票号码*/ constraint OrderMasterFK1 foreign key(customerNo) references Customer(customerNo), constraint OrderMasterFK2 foreign key(salerNo) references Employee(employeeNo) ) go

[chengxuan104]

go /*订单主表（sales）数据：*/ insert OrderMaster values('200801090001','C20050001','E2005002','20080109',0.00,'I000000001') insert OrderMaster values('200801090002','C20050004','E2005003','20080109',0.00,'I000000002') insert OrderMaster values('200801090003','C20080001','E2005002','20080109',0.00,'I000000003') insert OrderMaster values('200802190001','C20050001','E2005003','20080219',0.00,'I000000004') insert OrderMaster values('200802190002','C20070002','E2008002','20080219',0.00,'I000000005') insert OrderMaster values('200803010001','C20070002','E2007001','20080301',0.00,'I000000006') insert OrderMaster values('200803020001','C20050004','E2008003','20080302',0.00,'I000000007') insert OrderMaster values('200803090001','C20070003','E2008004','20080309',0.00,'I000000008') insert OrderMaster values('200805090001','C20060002','E2008002','20080509',0.00,'I000000009') insert OrderMaster values('200806120001','C20050001','E2005002','20080612',0.00,'I000000010') insert OrderMaster values('200901010001','C20050001','E2005002','20090101',0.00,'I000000011') insert OrderMaster values('201206070001','C20050004','E2005003','20120607',0.00,'I000000012') insert OrderMaster values('201206070002','C20070002','E2008002','20120607',0.00,'I000000013') go ------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------- /*订单明细表*/ print 'create OrderDetail' go create table OrderDetail( orderNo char(12) not null, /*订单编号*/ productNo char(9) not null, /*产品编号*/ quantity int not null, /*销售数量*/ price numeric(7,2) not null, /*成交单价*/ constraint OrderDetailPK primary key clustered(orderNo,productNo), constraint OrderDetailFK1 foreign key(orderNo) references OrderMaster(orderNo), constraint OrderDetailFK2 foreign key(productNo) references Product(productNo) ) go go /*订单明细表（OrderDetail）数据：*/ insert OrderDetail values('200801090001','P20050001',5 , 80.70) insert OrderDetail values('200801090001','P20050002',3 , 700.00) insert OrderDetail values('200801090001','P20050003',2 , 300.00) insert OrderDetail values('200802190001','P20060003',4 , 1200.00) insert OrderDetail values('200802190001','P20070001',2 , 256.00) insert OrderDetail values('200802190001','P20070002',5 , 200.00) insert OrderDetail values('200801090002','P20080001',2 , 890.00) insert OrderDetail values('200801090002','P20080003',2 , 900.00) insert OrderDetail values('200801090002','P20060002',5 , 66.00) insert OrderDetail values('200801090003','P20060001',3 , 200.60) insert OrderDetail values('200801090003','P20050001',5 , 80.70) insert OrderDetail values('200802190002','P20050003',2 , 300.00) insert OrderDetail values('200802190002','P20050005',3 , 100.60) insert OrderDetail values('200803010001','P20070001',4 , 256.00) insert OrderDetail values('200803010001','P20050001',8 , 80.70) insert OrderDetail values('200803020001','P20050001',2 , 80.70) insert OrderDetail values('200803020001','P20070003',3 , 100.00) insert OrderDetail values('200803020001','P20050002',1 , 700.00) insert OrderDetail values('200803090001','P20070002',5 , 200.00) insert OrderDetail values('200803090001','P20050003',4 , 300.00) insert OrderDetail values('200803090001','P20070001',2 , 256.00) insert OrderDetail values('200803090001','P20050004',5 , 35.00) insert OrderDetail values('200803090001','P20070004',3 , 320.00) insert OrderDetail values('200805090001','P20060003',8 , 1200.00) insert OrderDetail values('200805090001','P20070001',1 , 256.00) insert OrderDetail values('200805090001','P20070002',2 , 200.00) insert OrderDetail values('200805090001','P20070003',5 , 100.00) insert OrderDetail values('200806120001','P20050002',1 ,700.00) insert OrderDetail values('200806120001','P20050003',3 , 300.00) insert OrderDetail values('200806120001','P20050004',2 , 35.00) insert OrderDetail values('200806120001','P20050005',3 , 100.60) insert OrderDetail values('200901010001','P20050002',2 , 700.00) insert OrderDetail values('200901010001','P20050004',11 , 35.00) insert OrderDetail values('200901010001','P20050005',9 , 100.60) insert OrderDetail values('200901010001','P20080002',2 , 1100.00) insert OrderDetail values('201206070001','P20050003',10 , 300.00) insert OrderDetail values('201206070001','P20060002',5 , 66.00) insert OrderDetail values('201206070001','P20060003',4 , 1200.00) insert OrderDetail values('201206070002','P20060002',6 , 66.00) insert OrderDetail values('201206070002','P20070001',2 , 256.00) insert OrderDetail values('201206070002','P20070002',2 , 200.00) insert OrderDetail values('201206070002','P20080001',3 , 890.00) /*为便于调整订单成交价格，下列数据建议在调价触发器生效后插入*/ --insert OrderDetail values('200802190001','P20070003',3 , 100.00) --insert OrderDetail values('200802190001','P20070004',2 , 320.00) --insert OrderDetail values('200802190001','P20080001',2 , 890.00) --insert OrderDetail values('200802190001','P20080002',3 , 1100.00) --insert OrderDetail values('200802190001','P20080003',1 , 900.00) --insert OrderDetail values('200802190002','P20070003',4 , 100.00) --insert OrderDetail values('200802190002','P20070004',1 , 320.00) --insert OrderDetail values('200802190002','P20080001',3 , 890.00) --insert OrderDetail values('200801090002','P20080002',4 , 1100.00) --insert OrderDetail values('200806120001','P20060001',3 , 1200.60) --insert OrderDetail values('200806120001','P20060002',2 , 66.00) --insert OrderDetail values('200901010001','P20080003',1 , 900.00) go

[ForFumm]

CREATE PROCEDURE nzj4 @nianfen CHAR(10) 
AS
BEGIN
SELECT a.salerNo, '总销售额'=SUM(b.quantity), '奖金'= CASE WHEN SUM(b.quantity) < 5000 THEN SUM(b.quantity) * 1.15 
                                                           WHEN SUM(b.quantity) >= 5000 THEN SUM(b.quantity) * 1.25 
                                                           ELSE 0 END
                                                           FROM OrderMaster A LEFT JOIN OrderDetail B 
                                                           ON A.orderNo=B.orderNo 
                                                           WHERE DATEPART(YEAR,a.orderDate)=@nianfen
                                                           GROUP BY A.salerNo
                                                           ORDER BY A.salerNo
END;

EXEC nzj4 '2008'

[ForFumm]

--更正
CREATE PROCEDURE nzj4 @nianfen CHAR(10) 
AS
BEGIN
SELECT a.salerNo, '总销售额'=SUM(b.quantity*B.price), '奖金'= CASE WHEN SUM(b.quantity*B.price) < 5000 THEN SUM(b.quantity*B.price) * 1.15 
                                                           WHEN SUM(b.quantity*B.price) >= 5000 THEN SUM(b.quantity*B.price) * 1.25 
                                                           ELSE 0 END
                                                           FROM OrderMaster A LEFT JOIN OrderDetail B 
                                                           ON A.orderNo=B.orderNo 
                                                           WHERE DATEPART(YEAR,a.orderDate)=@nianfen
                                                           GROUP BY A.salerNo
                                                           ORDER BY A.salerNo
END;

EXEC nzj4 '2008'
/*
salerNo	总销售额	奖金
E2005002	9814.40	12268.0000
E2005003	21542.00	26927.5000
E2007001	1669.60	1920.0400
E2008002	15047.80	18809.7500
E2008003	1161.40	1335.6100
E2008004	3847.00	4424.0500
*/

[chengxuan104]

引用 4 楼 ForFumm 的回复:

SQL code?? 123456789101112131415161718192021222324 --更正 CREATE PROCEDURE nzj4 @nianfen CHAR(10) ASBEGINSELECT a.salerNo, '总销售额'=SUM(b.quantity*B.price), '奖金'= CASE WHEN SUM(b.quantity*B.pri……

不明白为什么那里要用左外连接，左外学的不是很好

[learningcoder]

SELECT D.salerNo,D.employeeName,Total,
	  CASE WHEN Total<5000 THEN Total*0.1
		   ELSE Total*0.15 END AS jiangjin
FROM (
		SELECT A.salerNo,C.employeeName,sum(B.price*B.quantity) AS Total FROM OrderMaster AS A
		JOIN OrderDetail AS B ON A.orderNo=B.orderNo
		JOIN Employee AS C ON A.salerNo=C.employeeNo
		WHERE YEAR(A.orderDate)=2008
		GROUP BY A.salerNo,C.employeeName
)AS D 
salerNo  employeeName Total                                   jiangjin
-------- ------------ --------------------------------------- ---------------------------------------
E2005002 张小梅          6080.60                                 912.090
E2005003 张小娟          10222.00                                1533.300
E2007001 吴浮萍          1669.60                                 166.960
E2008002 张良           11657.80                                1748.670
E2008003 黄梅莹          1161.40                                 116.140
E2008004 李虹冰          3847.00                                 384.700