应用面试题

[longai123]

product是产品销售明细表，所以id重复 id可以确定是哪个销售人员做成的单
sale是销售人员表
两表之间是通过id联系的
求2011年10月份销售排行榜(销售人员,bounds)
bounds=amount*price*0.05(10月份的提成)

[dawugui]

--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

[longai123]

谢谢大乌龟。好个大乌龟。向他学习....

[中国风]

这问题问过了么?

[中国风]

update Sale
set bounds=b.bounds
from 
(select ID,sum(amount*price*0.05) as bounds from product group by ID)b
where Sale.ID=a.ID

先得出Product同一ID的合计，再把合计更新到Sale.Bounds

[longai123]

还有月份的条件没有限定
只要10月份的
公司要统计销售软件人员的提成...

[稻草_木偶]

select a.* from
(select b.id,b.name,sum(c.amount*c.price*0.05)as bounds from sale b
left join product c on b.id=c.id where datepart(mm,aledate)=10 group by b.id,b.name)a
order by id

[dawugui]

[Quote=引用 5 楼 longai123 的回复:]
还有月份的条件没有限定
只要10月份的
公司要统计销售软件人员的提成...
[/Quote]*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.05),0) val from sale m left join product n on m.id = n.id and convert(varchar(7),n.saledate,120) = '2011-10' 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.05),0) val from sale m left join product n on m.id = n.id and convert(varchar(7),n.saledate,120) = '2011-10' 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.05),0) val from sale m left join product n on m.id = n.id and convert(varchar(7),n.saledate,120) = '2011-10' group by m.id , m.name) t1