SQL 子查询 面试题

数据库中有一个表名为“Order”的表，结构和数据如下：

 

请你按照下面要求写出sql语句

1.统计出每个地区的合同金额合计并按此倒序排列显示。

SELECT region,a.COUNT FROM (SELECT region,SUM(total) AS COUNT

FROM OrderTest GROUP BY region) a ORDER BY a.COUNT DESC;

2.统计出每个地区销售人员数量

SELECT  region,COUNT(*) FROM ordertest GROUP BY  region;

3.统计出每个地区合同金额最少的销售人员

SELECT e.* FROM ordertest e,

(SELECT MIN(total) minsales,sales ,region FROM ordertest

GROUP BY region) a

WHERE e.region = a.region

AND e.total =a.minsales;

4.统计出所有超过本地区合同金额平均值得合同及金额

内连接

SELECT e.* FROM ordertest e

JOIN (SELECT AVG(total) minsales,sales ,region FROM ordertest

GROUP BY region) a

ON e.region = a.region

AND e.total >a.minsales;

外连接

SELECT e.* FROM ordertest e

LEFT OUTER JOIN (SELECT AVG(total) minsales,sales ,region FROM ordertest

GROUP BY region) a

ON e.region = a.region

WHERE e.total >a.minsales;