sql收集

[行舟]

需求：
--按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

[iqlife]

--不明白你的意图,没有需求,就没有结果

select a, sum(b) as b1,
  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

[liuyi1985_2008]

学习了

[YY_MM_DD]

5月再来回贴...mark了..

[Blaider]

不会新方法，就等着5月份接分了……

[ORHCLE]

with temp1 as (select a, sum(b) sum_b1 from test group by a),
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;

[ssqtjffcu]

楼主的需要是不是有点问题，你的a列是1到50，都没有重复，还要分组做什么？

[sxdtgsh]

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)

[娃娃鱼]

学习了 在学习

[行舟]

[Quote=引用 8 楼 ssqtjffcu1 的回复:]
楼主的需要是不是有点问题，你的a列是1到50，都没有重复，还要分组做什么？
[/Quote]
那个匿名块只是用来模仿数据的，可以多次运行！

[行舟]

[Quote=引用 1 楼 java3344520 的回复:]
SQL code
--不明白你的意图,没有需求,就没有结果

select a, sum(b) as b1,
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 ……



[/Quote]
需求在上面啊！怎么还不明白了~

需求：
--按a列分组求出B列的和为: B(a),B列的和为:B
--求出B(a)>B/50的所有列
代码中我写的1/30！

[行舟]

我只是想看下。这样需求有多少中写法~
如果更加深入点，可以研究下各种写法的性能！