有两个表
Area表中存放着区域划分范围,区域划分使用地域编码起止区间方式,例如,华东地区的起止编码是10001~10003,在10001、10002、10003区域的客户都隶属于华东地区。
Sales存放着每个区域中客户的购买信息。
需求:统计出每个区域中购买量最大的客户。
创建两个表
CREATE TABLE Areas
(area_name char(25) NOT NULL,
start_co`在这里插入代码片`de int NOT NULL,
end_code int NOT NULL,
CHECK(start_code <= end_code));
CREATE TABLE Sales
(sale_id int,
cust_name char(15),
cust_code int,
sale_amt decimal (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);
建表如下
华东|10001|10003
华南|10004|10006
华北|10007|10009
1|张三|10001|1000
2|张三|10002|1000
3|李四|10001|4000
4|王五|10005|1000
5|王五|10006|2000
6|赵六|10004|1500
7|三三|10008|1321
8|四四|10007|1268
9|wuwu|10006|1132
分组计算出每个地区每个客户的总购买额,再使用窗口函数对每个地区按购买额排序
两表连接时,连接条件非等式。
select *,
row_number() over(partition by area_name order by sum_sale desc) as rk
from
(select area_name,
cust_name,
sum(sale_amt) sum_sale
from Sales s
left join Areas a on s.cust_code between a.start_code and a.end_code
group by area_name,cust_name) t
华东|李四|4000|1
华东|张三|2000|2
华北|三三|1321|1
华北|四四|1268|2
华南|王五|3000|1
华南|赵六|1500|2
华南|wuwu|1132|3
最后查询TOP1的客户。
select area_name,
cust_name
from
(select *,
row_number() over(partition by area_name order by sum_sale desc) as rk
from
(select area_name,
cust_name,
sum(sale_amt) sum_sale
from Sales s
left join Areas a on s.cust_code between a.start_code and a.end_code
group by area_name,cust_name) t
) s
where rk <= 1
华东|李四
华北|三三
华南|王五