用一条sql 找出大于自己地区平均值的人
算是面试题吧，没做出来，
table person
pid name
1 张三
2 李四
3 张二
4 王五
5 张一
6 李三
table sale
pid total region
1 12 华东
2 5 华南
3 8 华东
4 21 华北
5 6 华东
6 9 华南
[解决办法]
SELECT M.NAME 姓名,
N.REGION 所在区域,
N.TOTAL 自己销售值,
D.TOTAL 自己所在地区销售值
FROM PERSON M,
SALE N,
(SELECT T.REGION, AVG(T.TOTAL) TOTAL FROM SALE T GROUP BY T.REGION) D
WHERE M.PID = N.PID
AND N.REGION = D.REGION
AND N.TOTAL > D.TOTAL
/*
create table person (pid number(8) primary key, pname varchar2(20));
insert into person values(1,'zhangsan');
insert into person values(2,'lisi');
insert into person values(3,'zhanger');
insert into person values(4,'wangwu');
insert into person values(5,'zhangyi');
insert into person values(6,'lisan');
create table sale(pid number(8), total number(8), region varchar2(20));
insert into sale values(1,12,'huadong');
insert into sale values(2,5,'huanan');
insert into sale values(3,8,'huadong');
insert into sale values(4,21,'huabei');
insert into sale values(5,6,'huadong');
insert into sale values(6,9,'huanan');
*/
select p.pid,p.pname from sale
inner join person p on sale.pid = p.pid
inner join
(
select s.region,avg(s.total) as avgtotal from sale s
group by s.region
) temp on sale.region = temp.region
where sale.total > temp.avgtotal
with person as
(
select 1 pid,'张三' name from dual union all
select 2 pid,'李四' name from dual union all
select 3 pid,'张二' name from dual union all
select 4 pid,'王五' name from dual union all
select 5 pid,'张一' name from dual union all
select 6 pid,'李三' name from dual
),sale as
(
select 1 pid,12 total,'华东' region from dual union all
select 2 pid,5 total,'华南' region from dual union all
select 3 pid,9 total,'华东' region from dual union all
select 4 pid,21 total,'华东' region from dual union all
select 5 pid,6 total,'华东' region from dual union all
select 6 pid,9 total,'华南' region from dual
)
select *
from
(
select a.pid,a.name,b.total,b.region,
avg(total) over(partition by region order by region) ag
from person a,sale b
where a.pid = b.pid
)
where total > ag
order by pid
pid name total region ag
-------------------------------------------------
14王五21华东12
26李三9华南7
