一个关于高级查询的问题，纠结好久了。表已建好
--创建职员表
create table tbEmp
(
eID number primary key, --职员编号
eName varchar2(20) not null, --职员姓名
eSex varchar2(2) not null --职员性别
check(esex in ('男','女')),
eAge number not null check(eage>=18), --职员年龄
eAddr varchar2(50) not null, --职员地址
eTel varchar2(30) not null, --职员电话
eEmail varchar2(30) --职员邮箱
);
insert into tbemp---------------------
(eID,ename,esex,eage,eaddr,etel)
values
(1,'赵龙','男',25,'湖南省长沙市伍家岭江南苑9栋203号','0731-4230123');
insert into tbemp---------------------
(eID,ename,esex,eage,eaddr,etel)
values
(2,'李云','女',23,'湖南省长沙市东风路东风新村21栋502号','0731-4145268');
insert into tbemp---------------------
(eID,ename,esex,eage,eaddr,etel)
values
(3,'孙一成','男',24,'湖南省株洲市601厂宿舍15栋308号','0732-8342567');
insert into tbemp---------------------
(eID,ename,esex,eage,eaddr,etel)
values
(4,'林笑','男',27,'湖南省郴洲市人民医院20栋301号','0735-2245214');
insert into tbemp---------------------
(eID,ename,esex,eage,eaddr,etel)
values
(5,'卫晴','女',23,'湖南省长沙市望月湖12栋403号','0731-8325124');
--创建商品表
create table tbProd
(
pID number primary key, --商品编号
pType varchar2(20) not null, --商品类型
pMark varchar2(20) not null, --商品品牌
pSpec varchar2(20)--商品规格
);
insert into tbprod values(1,'电视机','长虹','29英寸纯平');
insert into tbprod values(2,'电视机','长虹','29英寸纯平艺术');
insert into tbprod values(3,'电视机','长虹','32英寸背投');
insert into tbprod values(4,'电视机','熊猫','29英寸纯平');
insert into tbprod values(5,'电视机','熊猫','29英寸纯平艺术');
insert into tbprod values(6,'电视机','熊猫','32英寸背投');
insert into tbprod values(7,'笔记本','联想','P4-1.8G');
insert into tbprod values(8,'笔记本','联想','P4-2.4G');
insert into tbprod values(9,'笔记本','紫光','P4-1.8G');
insert into tbprod values(10,'笔记本','紫光','P4-2.4G');
--创建销售情况表
create table tbSales
(
srNO number primary key, --记录编号
eID number not null references tbemp, --职员编号
pID number not null references tbprod, --商品编号
pQty number not null check(pqty>0), --销售数量
pAmount number(9,2) not null check(pamount>0), --销售价值
sDate date --销售时间
);
select * from tbemp;
select * from tbprod;
insert into tbsales values(1,1,1,10,21000,'12-3月-2004');
insert into tbsales values(2,1,2,5,20000,to_date('2004-3-12','yyyy-MM-dd'));
insert into tbsales values(3,1,4,12,23500,to_date('2004-3-14','yyyy-MM-dd'));
insert into tbsales values(4,1,5,4,16500,to_date('2004-3-14','yyyy-MM-dd'));
insert into tbsales values(5,2,3,3,31000,to_date('2004-3-11','yyyy-MM-dd'));
insert into tbsales values(6,2,6,4,40000,to_date('2004-3-13','yyyy-MM-dd'));
insert into tbsales values(7,3,7,5,40000,to_date('2004-3-13','yyyy-MM-dd'));
insert into tbsales values(8,3,8,3,36000,to_date('2004-3-14','yyyy-MM-dd'));
insert into tbsales values(9,4,9,6,41500,to_date('2004-3-12','yyyy-MM-dd'));
insert into tbsales values(10,4,10,5,50000,to_date('2004-3-14','yyyy-MM-dd'));
insert into tbsales values(11,1,1,10,21000,to_date('2004-4-12','yyyy-MM-dd'));
insert into tbsales values(12,1,2,5,20000,to_date('2004-4-12','yyyy-MM-dd'));
insert into tbsales values(13,1,4,12,23500,to_date('2004-4-14','yyyy-MM-dd'));
insert into tbsales values(14,1,5,4,16500,to_date('2004-4-14','yyyy-MM-dd'));
insert into tbsales values(15,2,3,3,31000,to_date('2004-4-11','yyyy-MM-dd'));
insert into tbsales values(16,2,6,4,40000,to_date('2004-4-13','yyyy-MM-dd'));
insert into tbsales values(17,3,7,5,40000,to_date('2004-4-13','yyyy-MM-dd'));
insert into tbsales values(18,3,8,3,36000,to_date('2004-4-14','yyyy-MM-dd'));
insert into tbsales values(19,4,9,6,41500,to_date('2004-4-12','yyyy-MM-dd'));
insert into tbsales values(20,4,10,5,50000,to_date('2004-4-14','yyyy-MM-dd'));
select * from tbemp;
select * from tbprod;
select * from tbsales;
--4. 找出销售总额最高的职员的姓名
select tbemp.ename, max(tbsales.pamount) from tbemp
inner join tbsales on tbemp.eid=tbsales.eid
group by tbemp.ename
--做到这里卡住了
[解决办法]
赵龙162000
李云142000
孙一成152000
林笑183000
我本机没有卡住。。不知道你卡住是什么意思，这么点数据量卡住的可能性太小了吧！
[解决办法]
----汗。。。
--4. 找出销售总额最高的职员的姓名
select ename from (
select tbemp.ename, sum(tbsales.pamount) from tbemp
inner join tbsales on tbemp.eid=tbsales.eid
group by tbemp.ename
order by sum(tbsales.pamount) desc
)
where rownum = 1;
[解决办法]
---一次性说清楚，谢谢！
select * from (
select tbemp.ename, sum(tbsales.pamount),rank()over(order by sum(tbsales.pamount) desc ) as rank_num from tbemp
inner join tbsales on tbemp.eid=tbsales.eid
group by tbemp.ename)
where rank_num = 1
[解决办法]
非分析函数的写法，性能应该要打折扣的
select tbemp.ename, sum(tbsales.pamount) from tbemp
inner join tbsales on tbemp.eid=tbsales.eid
group by tbemp.ename
having sum(tbsales.pamount) = (
select max(pamount) from
(select tbemp.ename, sum(tbsales.pamount) as pamount from tbemp
inner join tbsales on tbemp.eid=tbsales.eid
group by tbemp.ename
)
)
[解决办法]
请问你测试过没有？
另外，我已经告诉你 rank 这个分析函数了，请你百度下它的含义，再提出问题
oracle在排序方面有3个分析函数 rank,dense_rank,row_number，请你去看看再说
select tbemp.ename, sum(tbsales.pamount),rank()over(order by sum(tbsales.pamount) desc ) as rank_num
,dense_rank()over(order by sum(tbsales.pamount) desc )
,row_number()over(order by sum(tbsales.pamount) desc )
from tbemp
inner join tbsales on tbemp.eid=tbsales.eid
group by tbemp.ename
[解决办法]
[解决办法]
貌似是SELECT中使用聚合函数且有其他字段的时候，EG:SELECT EID,SUM(PAMOUNT) FROM TBSALES;
就会报上面的错误，需要对投影中的非聚合函数的字段分组，EG:SELECT EID,SUM(PAMOUNT) FROM TBSALES GROUP BY EID;
真纠结，PARTITION BY EID 会产生4条记录，ORDER BY SUM(PAMOUNT)产生一条记录，所以汇报错了。
是这样理解的吧？
[解决办法]
差不多是这个意思！
[解决办法]
这3个函数直接的区别是什么？主要表现在出现相同排序的问题处理上。
dense_rank() over()我叫他密集函数，当出现相同数据时，会默认相同数据有一样的编号，且接下来的编号不会跳跃
rank() over()函数，当出现相同数据时，会默认相同数据有一样的编号，且接下来的编号会跳跃
row_number() over()函数，仅对数据进行编号，而不考虑数据是否相同
[解决办法]
不结贴么？