-----传说是新浪面试题-----------
java考察的知识点有:设计模式,UML图,servlet的跳转(forward redirect),MVC流程图
sql考察:
SalesOrder 合同表
统计:
1.每个地区的销售总额并降序排列
2.每个地区的销售人员数量
3.每个地区销售金额最少的销售人员
4.各个地区所有超过每个地区合同额的平均值的合同,销售人员
orderID(主键) | 地区region | 销售人员 sale | 合同总额total |
1 | A | 张三 | 10000 |
2 | A | 李四 | 12000 |
3 | B | 王五 | 30000 |
4 | C | 小刘 | 20000 |
5 | C | 小马 |
23000
#SalesOrder 合同表
#统计:
#1.每个地区的销售总额并降序排列
select region, SUM(total) as su from sales_order
group by region
order by su desc;
#2.每个地区的销售人员数量
select region , COUNT(DISTINCT sale) as coun from sales_order
group by region;
#3.每个地区销售金额最少的销售人员
select region,sale,MIN(total) from sales_order
group by region;
#4.各个地区所有超过每个地区合同额的平均值的合同,销售人员
select region ,sale,total from sales_order as t1 ,
(
select region as r2,AVG(total)as av from sales_order
group by region
) as t2
where t1.region = t2.r2
and t1.total>t2.av
--------------------------------