请问一个mysql查询语句有关问题

请教一个mysql查询语句问题
已知表orders：region为地区，order为订单金额。id为订单号
idregionsalemanorder
1A张三1000.0
2A李四1200.0
3A张三1300.0
4A李四1000.0
5B王五1800.0
6B赵六1500.0
7C黄七1600.0
8C黄七1400.0
9C周八1550.0

求：找出不同区域的销售人员的订单金额大于本地区平均值的订单编号及销售人员。


跪求sql查询语句
 


[解决办法]

SQL code

with temp as(    select region, avg([order]) as avgOrder    from test    group by region)select test.*from testinner join temp on temp.region = test.regionwhere test.[order] > temp.avgOrder
[解决办法]
SQL code
SELECT * FROM orders AS oWHERE `order` > (SELECT AVG(`order`) FROM orders                 WHERE o.region=region)