现有一个表:
person product product_count
wang tv 2
wang cd 3
li dvd 1
li vcd 10
li phone 5我希望得到
person product_type_count product_total_count
wang 2 5
li 3 16
可否直接写查询语句实现
如果不能,通过存储过程或函数,如何实现?小弟先感谢各位看官的了,如蒙指点,不胜感激!!
person product product_count
wang tv 2
wang cd 3
li dvd 1
li vcd 10
li phone 5我希望得到
person product_type_count product_total_count
wang 2 5
li 3 16
可否直接写查询语句实现
如果不能,通过存储过程或函数,如何实现?小弟先感谢各位看官的了,如蒙指点,不胜感激!!
person varchar2(10),
product varchar2(10),
product_count number
);insert into pro values('wang','tv',2);
insert into pro values('wang','cd',3);
insert into pro values('li','dvd',1);
insert into pro values('li','vcd',10);
insert into pro values('li','phone',5);commit;
1: 用分析函数-------------------------select distinct p.person,count(p.product) over(partition by p.person) as product_type_count,tt.product_total_count
from pro p,
(
select pp.person,sum(pp.product_count) as product_total_count
from pro pp
group by pp.person
)tt
where p.person = tt.person;2:一般的方法----------------------- select yy.person,yy.product_type_count,tt.product_total_count
from (
select p.person,count(p.product) as product_type_count
from pro p
group by p.person
)yy,
(
select pp.person,sum(pp.product_count) as product_total_count
from pro pp
group by pp.person
)tt
where yy.person = tt.person;
结果:---------------------person product_type_count product_total_count
wang 2 5
li 3 16
from pro p
group by p.person;就这样就可以了,我想复杂了,哎~~ 回家看基础去~~ 郁闷!!~~~~!!!!!!!