锋利的SQL:地域范围内最大数统计
这是在做一个客户管理
系统时遇到的问题，公司每年需要按销售区域评选出购买量最大的客户进行单独奖励。区域划分使用地域编码起止区间方式，例如，华东地区的起止编码是10001～10003，在10001、10002、10003区域的客户都隶属于华东地区。下面是创建示例的代码，Area表中存放着区域划分范围，Sales存放着每个区域中客户的购买信息。
www.2cto.com
CREATE TABLE Areas
(area_name char(25) NOT NULL,
start_codeint NOT NULL,
end_codeint NOT NULL,
CHECK(start_code <= end_code));
CREATE TABLE Sales
(sale_id int,
cust_namechar(15),
cust_codeint,
sale_amtdecimal (8,2));
INSERT INTO Areas
VALUES ('华东', 10001, 10003),
('华南', 10004, 10006),
('华北', 10007, 10009);
INSERT INTO Sales
VALUES (1, '张三', 10001, 1000.00),
(2, '张三', 10002, 1000.00),
(3, '李四', 10001, 4000.00),
(4, '王五', 10005, 1000.00),
(5, '王五', 10006, 2000.00),
(6, '赵六', 10004, 1500.00);
www.2cto.com
要统计出每个区域中购买量最大的客户，我们首先想到的是使用GROUP BY子句进行分类汇总，参考下面的语句：
SELECT A.area_name, S.cust_name, SUM(S.sale_amt)AS sale_amt
FROM Areas AS A
JOIN SalesAS S
ONS.cust_code BETWEEN A.start_code AND A.end_code
GROUP BY A.area_name, S.cust_name
ORDER BY area_name;
表19-12 使用GROUP BY按区域、客户分类汇总结果
area_name
cust_name
sale_amt
华东
李四
4000.00
华东
张三
2000.00
华南
王五
3000.00
华南
赵六
1500.00
从上表可以看出，在华东地区李四的购买量最大，华南地区王五的购买量最大。如果仅统计每地区的最大销售额，不考虑客户名称，完全可以在上表的基础上使用类似下面的语句：
SELECT area_name, MAX(sale_amt)
FROM [表19-21]
GROUP BY area_name;
下面的语句将GROUP BY分组统计封装在CTE中，然后将CTE打开两次，取出每区域中购买量最大的客户。查询结果如表19-13所示。
WITH CTE (area_name, cust_name, sale_amt)
AS (SELECT A.area_name, S.cust_name,SUM(S.sale_amt)
FROMAreas AS A
JOINSales AS S
ONS.cust_code BETWEEN A.start_code AND A.end_code
GROUP BYA.area_name, S.cust_name)
SELECT C1.area_name, C1.cust_name, C1.sale_amt
FROM CTE AS C1
WHERE C1.sale_amt = (SELECT MAX(C2.sale_amt)
FROM CTE AS C2
WHERE C2.area_name =C1.area_name);
表19-13 查询结果
area_name
cust_name
sale_amt
华南
王五
3000.00
华东
李四
4000.00