查询问题~各位请进

Question

select ...,1 一月,sum(decode(relief_month,1,relief_value,0)),
2 二月,sum(decode(relief_month,2,relief_value,0)),
...
12 十二月,sum(decode(relief_month,12,relief_value,0))
from view_name
group by request_id;

Answer 1

select request_id, idcard_id, name, age, sex, relief_statu,1 as 1月, (select sum
(relief_value) from View1 where relief_month =1),2 as 2月, (select sum(relief_va
lue) from View1 where relief_month =2),...,12 as 12月, (select sum(relief_va
lue) from View1 where relief_month =12)
 FROM VIEW1

Answer 2

谢谢bzszp(SongZip)的帮助.
我想问relief_value可能是有3种情况,不是固定为0
request_id idcard_id name age sex relief_statu 一月 relief_value...十二月 relief_value
1000000001 001222220 ddd  55  boy    0          1 100 2 200 ....12 1200
1000000002 212122221 ccc  12  boy    1          1 122 2 222 ....12 1222
.....................................那应该怎样??

Answer 3

同意版主使用的decode:
select request_id,idcard_id,name,age,sex,relief_statu,sum(decode(relief_month,1,relief_value,0)) jan,sum(decode(relief_month,2,relief_value,0)) feb,...sum(decode(relief_month,12,relief_value,0)) dec from your_view_name
 group by request_id;

Answer 4

...group by request_id,relief_statu;

Answer 5

relief_value可能是有3种情况
??
relief_statu??

Answer 6

SORRY~应该是relief_statu有三种情况~
select family_unit_accept_id,applicant_idcard_id,applicant_name,family_unit_apply_id,family_ensure_people_number,number_age,sum(decode(RELIEF_MONTH,'1',relief_item_value,'0')),sum(decode(RELIEF_MONTH,'2',relief_item_value,'0')) from v_money_send t group by family_unit_accept_id我试了一下上面语句,不过说不是GROUD BY 表达式.

Answer 7

看表记录，作这样查询绝对不合理，估计半个小时也查不出来。
建义新建另一表，作查询。
以后利用分析函数作子查询.
select request_id,idcard_id,name,age,sex,relief_statu,
       sum(decode(relief_month,'1','1')) 一月,
       sum(deocde(substr(relief_value,-1,1),'1',substr(relief_value,1,length(relief_value)-1)) 一月值,
       sum(deocde(relief_month,'2','2')) 二月,
       sum(deocde(substr(relief_value,-1,1),'2',substr(relief_value,1,length(relief_value)-1)) 二月值,
       sum(deocde(relief_month,'3','3')) 三月,
       sum(deocde(substr(relief_value,-1,1),'3',substr(relief_value,1,length(relief_value)-1)) 三月值,
       ...
from (select request_id,idcard_id,name,age,sex,relief_statu,relief_month,sum(relief_value) over(partition by request_id,idcard_id,name,age,sex,relief_statu,relief_month order by relief_month)||relief_month relief_value from table_name)  group by request_id,idcard_id,name,age,sex,relief_statu

Answer 8

select family_unit_accept_id,applicant_idcard_id,applicant_name,family_unit_apply_id,family_ensure_people_number,number_age,sum(decode(RELIEF_MONTH,'1',relief_item_value,'0')),sum(decode(RELIEF_MONTH,'2',relief_item_value,'0')) from v_money_send t group by family_unit_accept_id,applicant_idcard_id,applicant_name,family_unit_apply_id,family_ensure_people_number,number_age

Answer 9

select family_unit_accept_id,applicant_idcard_id,applicant_name,family_unit_apply_id,family_ensure_people_number,number_age,
sum(decode(RELIEF_MONTH,'1',relief_state,'0')) as onestate,
sum(decode(RELIEF_MONTH,'1',relief_item_value,'0')) as onevalue,
sum(decode(RELIEF_MONTH,'2',relief_state,'0')) as twostate,
sum(decode(RELIEF_MONTH,'2',relief_item_value,'0')) as twovalue 
from v_money_send t where help_type='062001' and rownum<600 group by family_unit_accept_id,applicant_idcard_id,applicant_name,family_unit_apply_id,family_ensure_people_number,number_age最后我写成这个样子~还想问问这个SQL可以用RODER BY 吗??而且性能会怎样??
因为我现在用来测试的数据只有原来的十分一,所以速度很快~一下就出来了~不知道数据大了会不会有问题??