select substr((num/
(select sum(num) from researchoption where noteask_id in
(select noteask_id from research group by noteask_id)
)
)*100,0,2) from researchoption;
我用这个返回对了
可我把他用到视图时
create or replace view vresearchoption as
select noteask_id,option_id,researchoption,
(select substr((num/
(select sum(num) from researchoption where noteask_id in
(select noteask_id from research group by noteask_id)
)
)*100,0,2) from researchoption) as num,
to_char(optiondate,'yyyy-MM-dd hh:mm') as optiondate
from researchoption ;
又不对了
(select sum(num) from researchoption where noteask_id in
(select noteask_id from research group by noteask_id)
)
)*100,0,2) from researchoption;
我用这个返回对了
可我把他用到视图时
create or replace view vresearchoption as
select noteask_id,option_id,researchoption,
(select substr((num/
(select sum(num) from researchoption where noteask_id in
(select noteask_id from research group by noteask_id)
)
)*100,0,2) from researchoption) as num,
to_char(optiondate,'yyyy-MM-dd hh:mm') as optiondate
from researchoption ;
又不对了
2 researchoption(
3 NOTEASK_ID varchar2(10),
4 OPTION_ID varchar2(10),
5 num numeric);表已创建。SQL> insert into researchoption
2 values(
3 1, 1, 20);已创建 1 行。SQL> insert into researchoption
2 values(
3 1, 2, 50);已创建 1 行。SQL> insert into researchoption
2 values(
3 2, 3, 60);已创建 1 行。SQL> insert into researchoption
2 values(2, 4, 30);已创建 1 行。SQL> select * from researchoption;NOTEASK_ID OPTION_ID NUM
---------- ---------- ----------
1 1 20
1 2 50
2 3 60
2 4 30SQL> create view v_sum as
2 select noteask_id,sum(num) all_sum from researchoption
3 group by noteask_id;视图已建立。SQL> select a.noteask_id,a.option_id,num, num/b.all_sum * 100,b.all_sum
2 from researchoption a,v_sum b
3 where a.noteask_id = b.noteask_id;NOTEASK_ID OPTION_ID NUM NUM/B.ALL_SUM*100 ALL_SUM
---------- ---------- ---------- ----------------- ----------
1 1 20 28.5714286 70
1 2 50 71.4285714 70
2 3 60 66.6666667 90
2 4 30 33.3333333 90
谢了,可以实现还有没有其他方法,就是只建一个view
2 /RO CLAS PR
-- ---- --
01 1 20
01 2 50
02 3 60
02 4 30SQL> ed
Wrote file afiedt.buf 1 select A.row_no, A.class_no , trunc(A.price_no/B.sum,2)*100
2 from test_table A,
3 (
4 select row_no , sum(price_no) as sum
5 from test_table
6 group by row_no
7 ) B
8* where A.row_no = B.row_no
SQL> /RO CLAS TRUNC(A.PRICE_NO/B.SUM,2)*100
-- ---- -----------------------------
01 1 28
01 2 71
02 3 66
02 4 33SQL>
补充:最后少了a,round没有取小数位
select row_no,class_no,
round(price_no/(select sum(price_no) from test_table where row_no=a.row_no group by row_no),2)*100
from test_table a
;