应用面试题
product是产品销售明细表，所以id重复 id可以确定是哪个销售人员做成的单
sale是销售人员表
两表之间是通过id联系的
求2011年10月份销售排行榜(销售人员,bounds)
bounds=amount*price*0.05(10月份的提成)
[解决办法]
--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
[解决办法]
这问题问过了么?
[解决办法]
