需求:
--按a列分组求出B列的和为: B(a),B列的和为:B
--求出B(a)>B/50的所有列
表、模拟数据
create table test(a number,b number);
begin
for i in 1..50
loop
insert into test values(i,trunc(dbms_random.value*100));
end loop;
end;
我先给出三种写法:楼下补充啊!5月份结贴!--传统
select a, sum(b)
from test
group by a
having sum(b) > (select sum(b) / 30 from test);
--9i以后
with temp as (select a, sum(b) sum_b from test group by a)
select a, sum_b from temp where sum_b > (select sum(sum_b) / 30 from temp)
--
select a,sum_b
from (select distinct a,
sum(b) over(partition by a) sum_b,
sum(b) over() / 30 total_b
from test)
where sum_b > total_b
--按a列分组求出B列的和为: B(a),B列的和为:B
--求出B(a)>B/50的所有列
表、模拟数据
create table test(a number,b number);
begin
for i in 1..50
loop
insert into test values(i,trunc(dbms_random.value*100));
end loop;
end;
我先给出三种写法:楼下补充啊!5月份结贴!--传统
select a, sum(b)
from test
group by a
having sum(b) > (select sum(b) / 30 from test);
--9i以后
with temp as (select a, sum(b) sum_b from test group by a)
select a, sum_b from temp where sum_b > (select sum(sum_b) / 30 from temp)
--
select a,sum_b
from (select distinct a,
sum(b) over(partition by a) sum_b,
sum(b) over() / 30 total_b
from test)
where sum_b > total_b
from test
group by a
having sum(b) > sum(b)/30 ;select a,b1 from
(select a, sum(b) as b1, sum(b)/30 as b2,
from test
group by a)
where b1>b2
temp2 as (select sum(b)/30 sum_b2 from test)
select temp1.a, temp1.sum_b1
from temp1,temp2
where temp1.sum_b1 > temp1.sum_b2;
--按a列分组求出B列的和为: B(a),B列的和为:B
--求出B(a)>B/50的所有列
代码中我写的1/30!
select ttest_a.a, sum(ttest_a.b)
from ttest_a, (select (sum(b) / 30) c
from ttest_a) ttest_sumb
group by ttest_a.a
having sum(ttest_a.b)> max(ttest_sumb.c)