大牛再来
求2011年10月份的销售提成
(销售人员,bounds)
product是产品销售明细表所以id重复 id可以确定是哪个销售人员做的单
bounds=amount*price*0.05
贴上大乌龟的代码
--sql 2005
select t.* , RANK() over(order by val desc) 排名 from
(
select m.id , m.name , isnull(sum(n.amount* n.price),0) val from sale m left join product n on m.id = n.id group by m.id , m.name
) t
--sql 2000
select t1.* , (select count(val) from (select m.id , m.name , isnull(sum(n.amount* n.price),0) val from sale m left join product n on m.id = n.id group by m.id , m.name) t2 where t2.val > t1.val) + 1
from (select m.id , m.name , isnull(sum(n.amount* n.price),0) val from sale m left join product n on m.id = n.id group by m.id , m.name) t1
只要2011年10月份的提成
急啊。我明天一定要做出来。不然销售部就不能发工资了。。
[解决办法]
create table sale(id varchar(10),name nvarchar(10))insert into sale select '01','龙海'insert into sale select '02','黄苑超'insert into sale select '03','某某'create table product(id varchar(10),productname nvarchar(10),amount int,price decimal(8,2),saledate datetime)insert into product select '01','面包',10,2,'2011-6-8'insert into product select '02','fsa',15,14,'2011-7-8'insert into product select '01','faw',222,1.1,'2011-10-25'insert into product select '03','fwas',110,1.4,'2011-6-30'insert into product select '02','aedsa',44,1.5,'2011-10-27'insert into product select '03','w3sa',88,1.5,'2011-10-31'goselect a.name,sum(b.amount*b.price*0.05)提成from sale a inner join product b on a.id=b.idwhere b.saledate between '2011-10-01' and '2011-10-31'group by a.name/*name 提成---------- ---------------------------------------黄苑超 3.3000龙海 12.2100某某 6.6000(3 行受影响)*/godrop table sale,product
[解决办法]
select t.* , RANK() over(order by val desc) 排名 from( select m.id , m.name , isnull(sum(n.amount* n.price),0) val,n.saledate from sale m left join product n on m.id = n.id group by m.id , m.name) twhere CONVERT(varchar(7),t.saledate ,120)='2011-10'
[解决办法]
select *from (select ID,sum(amount*price*0.05) as bounds from product where convert(varchar(7),SaleDate,120)='2011-10' group by ID)bwhere Sale.ID=a.ID
