declare @emp_no char(8),@emp_name char(10),@dept char(10),@cus_no char(9),@pro_no char(9)
declare @quantity int,@p_tot int,@text char(100)
declare @price numeric(7,2),@q_tot int
declare emp_cur cursor for
select employeeNo,employeeName,department,b.customerNo,productNo,c.quantity,c.price,sum(orderSum),count(*)
from Employee a, OrderMaster b,Orderdetail c
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and sex='女'
group by employeeNo,employeeName,department,b.customerNo,productNo,quantity,price
open emp_cur
fetch next into @emp_no,@emp_name,@dept,@cus_no ,@pro_no,@quantity,@price,@p_tot,@q_tot
begin
select @text='员工编号:'+@emp_no
print @text
select @text='员工姓名:'+@emp_name
print @text
select @text='所属部门:'+@dept
print @text
select @text='客户号 '+'商品编号 '+'销售数量 '+'成交单价 '
print @text
select @text=@emp_no+' '+@pro_no+' '+@quantity+' '+@price+' '
print @text
select @text='订单数量:'+@q_tot
print @text
select @text='销售总额:'+@p_tot
print @text
end
close emp_cur
deallocate emp_cur
declare @quantity int,@p_tot int,@text char(100)
declare @price numeric(7,2),@q_tot int
declare emp_cur cursor for
select employeeNo,employeeName,department,b.customerNo,productNo,c.quantity,c.price,sum(orderSum),count(*)
from Employee a, OrderMaster b,Orderdetail c
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and sex='女'
group by employeeNo,employeeName,department,b.customerNo,productNo,quantity,price
open emp_cur
fetch next into @emp_no,@emp_name,@dept,@cus_no ,@pro_no,@quantity,@price,@p_tot,@q_tot
begin
select @text='员工编号:'+@emp_no
print @text
select @text='员工姓名:'+@emp_name
print @text
select @text='所属部门:'+@dept
print @text
select @text='客户号 '+'商品编号 '+'销售数量 '+'成交单价 '
print @text
select @text=@emp_no+' '+@pro_no+' '+@quantity+' '+@price+' '
print @text
select @text='订单数量:'+@q_tot
print @text
select @text='销售总额:'+@p_tot
print @text
end
close emp_cur
deallocate emp_cur
因为不知道你的表结构,没法检查你哪儿有问题.
执行一下这一句:
select employeeNo,employeeName,department,b.customerNo,productNo,c.quantity,c.price,sum(orderSum),count(*)
from Employee a, OrderMaster b,Orderdetail c
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and sex='女'
group by employeeNo,employeeName,department,b.customerNo,productNo,quantity,price要有记录输出才行.
declare @quantity int,@p_tot int,@text char(100)
declare @price numeric(7,2),@q_tot int
declare emp_cur cursor for
select employeeNo,employeeName,department,b.customerNo,productNo,c.quantity,c.price,sum(orderSum),count(*)
from Employee a, OrderMaster b,Orderdetail c
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and sex='女'
group by employeeNo,employeeName,department,b.customerNo,productNo,quantity,price
open emp_cur
fetch next into @emp_no,@emp_name,@dept,@cus_no ,@pro_no,@quantity,@price,@p_tot,@q_tot
while @@FETCH_STATUS=0
begin
select @text='员工编号:'+@emp_no
print @text
select @text='员工姓名:'+@emp_name
print @text
select @text='所属部门:'+@dept
print @text
select @text='客户号 '+'商品编号 '+'销售数量 '+'成交单价 '
print @text
select @text=@emp_no+' '+@pro_no+' '+@quantity+' '+@price+' '
print @text
select @text='订单数量:'+@q_tot
print @text
select @text='销售总额:'+@p_tot
print @text
fetch next into @emp_no,@emp_name,@dept,@cus_no ,@pro_no,@quantity,@price,@p_tot,@q_tot
end
close emp_cur
deallocate emp_cur
while @@FETCH_STATUS=0
fetch next into @emp_no,@emp_name,@dept,@cus_no ,@pro_no,@quantity,@price,@p_tot,@q_tot