一个sql语句 怎么写比较好

原因:如果这么写,CAPACITY 就计算错了.因为对应一个user_no来说,可能在Df_Money_Files_h存在2条记录,那么在TRANS_RUN 表中能取到2个值,那样不是我期待的结果,我系统TRANS_RUN有几个user_no 统计几个capacity. select count( distinct a.user_no), sum(nvl((SELECT T.CAPACITY FROM TRANS_RUN T WHERE t.trans_state <> 'S' and T.USER_NO =a.user_no),0)), sum(nvl((SELECT sum(nvl(T.CAPACITY,0)) FROM TRANS_RUN T WHERE t.trans_state <> 'S' and T.USER_NO = a.user_no),0)) as CAPACITY , round(sum(nvl(a.total_power,0))/10000,0)--根据需求 不带小数点 from Df_Money_Files_h a,df_write_sect b,busi_code_refer c where a.write_sect_no = b.write_sect_no and b.business_place_code = c.business_place_code and c.arg_busi_place_code = 'KFDLJN01' and a.mon = b.mon and b.mon =TO_DATE('2005-08-01','YYYY-MM-DD') and a.status in ('C','A','P') and a.price_code not in ('000') -- 排除不计费 and a.user_no not in (select user_no from ds_ms_point) --不含趸售 group by nvl(a.trade_type_code,'9910'); 没有办法,最好只能暂时这样解决了;用下面的sql,虽然效率底下: select max(w.row_sn),max(w.trade_type_code),max(w.row_title), sum (w.user_no) as user_num ,sum(w.capacity)as capacity,sum(w.total_power) as total_power from ( select max((select e.row_sn from report_row_info e where e.row_index_code = nvl(a.trade_type_code,'9910') and e.report_code = 'DFREPORT_HYYDFLB')) as row_sn, max(nvl(a.trade_type_code,'9910')) as trade_type_code, max((select e.row_title from report_row_info e where e.row_index_code = nvl(a.trade_type_code,'9910') and e.report_code = 'DFREPORT_HYYDFLB')) as row_title , count( distinct a.user_no) as user_no, ( nvl((SELECT sum(nvl(T.CAPACITY,0)) FROM TRANS_RUN T WHERE t.trans_state <> 'S' and a.USER_NO = t.user_no ),0) ) as capacity, round(sum(nvl(a.total_power,0))/10000,0) as total_power --根据需求 不带小数点 from df_money_files_h a,df_write_sect b,busi_code_refer c where a.write_sect_no = b.write_sect_no and b.business_place_code = c.business_place_code and c.arg_busi_place_code = 'KFDLJN01' and a.mon = b.mon and b.mon = TO_DATE('2005-08-01','YYYY-MM-DD') and a.status in ('C','A','P') and a.price_code not in ('000') -- 排除不计费 and a.user_no not in (select user_no from ds_ms_point) --不含趸售 group by a.user_no) w group by nvl(w.trade_type_code,'9910');