日期:2014-05-17 浏览次数:21058 次
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