统计类型的sql语句数据库数据：???teamName?? ??userName?? ??monthIncome?? ? A? 111? 60? A??222? 90? B?
统计类型的sql语句
数据库数据：
?
??teamName?? ??userName?? ??monthIncome?? ? A? 111? 60? A??222? 90? B? 333? 100? B? 444? 50?
select
? case userName when '小? 计' then '小? 计' else teamName end teamName,
? case userName when '小? 计' then '' else userName end userName,
??monthIncome
?from （
???????? select 0 ordersn, teamName, userName, monthIncome from table
???????? union
???????? select 1 ordersn, teamName, '小? 计' userName, sum(monthIncome) monthIncome?\
????????? group by?teamName
? ）
order by ordersn, teamName
?
?
参考：
select case userName when '小 计' then '小 计' else team_Name end teamName, userId, case userName when '小 计' then '' else userName end userName, brokerLevel, adjustDraftLevel, adjustDraftResult, draftLevel, draftResult, ifReport, detailRemark, branchCode, capital, capitalDaily, tranMoneyTotal, actualFeeTotal, netFeeTotal, netFeeConvertTotal, netFeeSum, netFeeMonthly, trunc(validCust) validCust, trunc(netValidCust) netValidCust, trunc(netValidCustConvert) netValidCustConvert, trunc( netValidCustSum) netValidCustSum, trunc(netValidCustMonthly) netValidCustMonthly from (<!-- 团队成员--><!--nvl(utr.team_name,'团队名称')目的是防止为空 导出excel判断相同单元格合并报错 --> select 1 ordersn, 0 orderno, nvl(utr.team_name,'团队名称') team_name,u.user_id userId,u.username userName, pd.START_LEVEL brokerLevel, PADJ.adjusted_level adjustDraftLevel, pd.draft_level draftLevel, PADJ.adjusted_result adjustDraftResult, pd.draft_result draftResult, pd.if_report ifReport, PADJ.ADJUSTED_REMARK detailRemark, PR.BRANCH_CODE branchCode, to_char(nvl((pd.capital),0),'99999999999999990.99') capital, to_char(nvl((pd.capital_daily),0),'99999999999999990.99') capitalDaily, to_char(nvl(( pd.tran_money_total),0),'99999999999999990.99') tranMoneyTotal, to_char(nvl((pd.actual_fee_total),0),'99999999999999990.99') actualFeeTotal, to_char(nvl((pd.net_fee_total),0),'99999999999999990.99') netFeeTotal, to_char(nvl((pd.net_fee_convert_total),0),'99999999999999990.99') netFeeConvertTotal, to_char(nvl(( pd.net_fee_sum),0),'99999999999999990.99') netFeeSum, to_char(nvl(( pd.net_fee_monthly),0),'99999999999999990.99') netFeeMonthly, to_char(nvl(( pd.valid_cust),0),'99999999999999990.99') validCust, to_char(nvl(( pd.net_valid_cust),0),'99999999999999990.99') netValidCust, to_char(nvl(( pd.net_valid_cust_convert),0),'99999999999999990.99') netValidCustConvert, to_char(nvl((pd.net_valid_cust_sum),0),'99999999999999990.99') netValidCustSum, to_char(nvl(( pd.net_valid_cust_monthly),0),'99999999999999990.99') netValidCustMonthly from kh.tb_snap_user u inner join kh.tb_kh_personal_detail pd on pd.user_id=u.user_id left join KH.TB_KH_PERSONAL_ADJUSTED PADJ on pd.p_detail_id=PADJ.p_detail_id inner join KH.TB_KH_PERSONAL_RESULT PR on PR.P_RESULT_ID=pd.P_RESULT_ID and PR.status = '1' inner join (select utr.user_id,ut.team_id,ut.team_name from kh.tb_snap_user_team_rel utr inner join kh.tb_snap_user_team ut on utr.team_id = ut.team_id and ut.team_sts = 0 where utr.member_type=0 and utr.status = 0) utr on u.user_id = utr.user_id where u.user_type=0 and u.cur_state=0 and pd.IF_REPORT='1' and PR.AUDIT_STS in ('4','5','7') and PR.BRANCH_CODE in ('1010')and PR.RESULT_MONTH = '201012'union<!--团队小计-->select 1 ordersn, 1 orderno, utr.team_name,null userId,'小 计' userName, null brokerLevel, '' adjustDraftLevel, '' draftLevel, '' adjustDraftResult, '' draftResult , '' ifReport, '' detailRemark, PR.BRANCH_CODE branchCode, to_char(nvl(sum(to_char(pd.capital,'99999999999999990.99')),0)) capital, to_char( nvl(sum(to_char(pd.capital_daily,'99999999999999990.99')),0)) capitalDaily, to_char(nvl(sum(to_char( pd.tran_money_total,'99999999999999990.99')),0)) tranMoneyTotal, to_char( nvl(sum(to_char(pd.actual_fee_total,'99999999999999990.99')),0)) actualFeeTotal, to_char( nvl(sum(to_char(pd.net_fee_total,'99999999999999990.99')),0)) netFeeTotal, to_char(nvl(sum(to_char(pd.net_fee_convert_total,'99999999999999990.99')),0)) netFeeConvertTotal, to_char(nvl(sum(to_char( pd.net_fee_sum,'99999999999999990.99')),0)) netFeeSum, to_char(nvl(sum(to_char( pd.net_fee_monthly,'99999999999999990.99')),0)) netFeeMonthly, to_char(nvl(sum(to_char( pd.valid_cust,'99999999999999990.99')),0)) validCust, to_char( nvl(sum(to_char( pd.net_valid_cust ,'99999999999999990.99')),0)) netValidCust, to_char(nvl(sum(to_char( pd.net_valid_cust_convert,'99999999999999990.99')),0)) netValidCustConvert, to_char(nvl(sum(to_char(pd.net_valid_cust_sum,'99999999999999990.99')),0)) netValidCustSum, to_char(nvl(sum(to_char( pd.net_valid_cust_monthly,'99999999999999990.99')),0)) netValidCustMonthly from kh.tb_snap_user u inner join kh.tb_kh_personal_detail pd on pd.user_id=u.user_id inner join KH.TB_KH_PERSONAL_RESULT PR on PR.P_RESULT_ID=pd.P_RESULT_ID and PR.status = '1' inner join ( select utr.user_id,ut.team_id,ut.team_name from kh.tb_snap_user_team_rel utr inner join kh.tb_snap_user_team ut on utr.team_id = ut.team_id and ut.team_sts = 0 where utr.member_type=0 and utr.status = 0) utr on u.user_id = utr.user_id where u.user_type=0 and u.cur_state=0 and pd.IF_REPORT='1' and PR.AUDIT_STS in ('4','5','7') and PR.BRANCH_CODE in ('1010') and PR.RESULT_MONTH = '201012' group by PR.BRANCH_CODE, utr.team_nameunion<!--非团队成员-->select 2 ordersn, 0 orderno, '非团队成员' as team_Name, u.user_id userId , u.username userName, pd.START_LEVEL brokerLevel, PADJ.adjusted_level adjustDraftLevel, pd.draft_level draftLevel, PADJ.adjusted_result adjustDraftResult, pd.draft_result draftResult, pd.if_report ifReport, PADJ.ADJUSTED_REMARK detailRemark, PR.BRANCH_CODE branchCode, to_char(nvl((pd.capital),0),'99999999999999990.99') capital, to_char(nvl((pd.capital_daily),0),'99999999999999990.99') capitalDaily, to_char(nvl(( pd.tran_money_total),0),'99999999999999990.99') tranMoneyTotal, to_char(nvl((pd.actual_fee_total),0),'99999999999999990.99') actualFeeTotal, to_char(nvl((pd.net_fee_total),0),'99999999999999990.99') netFeeTotal, to_char(nvl((pd.net_fee_convert_total),0),'99999999999999990.99') netFeeConvertTotal, to_char(nvl(( pd.net_fee_sum),0),'99999999999999990.99') netFeeSum, to_char(nvl(( pd.net_fee_monthly),0),'99999999999999990.99') netFeeMonthly, to_char(nvl(( pd.valid_cust),0),'99999999999999990.99') validCust, to_char(nvl(( pd.net_valid_cust),0),'99999999999999990.99') netValidCust, to_char(nvl(( pd.net_valid_cust_convert),0),'99999999999999990.99') netValidCustConvert, to_char(nvl((pd.net_valid_cust_sum),0),'99999999999999990.99') netValidCustSum, to_char(nvl(( pd.net_valid_cust_monthly),0),'99999999999999990.99') netValidCustMonthly from kh.tb_snap_user u inner join kh.tb_kh_personal_detail pd on pd.user_id=u.user_id left join KH.TB_KH_PERSONAL_ADJUSTED PADJ on pd.p_detail_id=PADJ.p_detail_id inner join KH.TB_KH_PERSONAL_RESULT PR on PR.P_RESULT_ID=pd.P_RESULT_ID and PR.status = '1' left join ( select utr.user_id from kh.tb_snap_user_team_rel utr inner join kh.tb_snap_user_team ut on utr.team_id = ut.team_id and ut.team_sts = 0 where utr.status = 0) utr on u.user_id = utr.user_id where utr.user_id is null and u.user_type=0 and u.cur_state=0 and pd.IF_REPORT='1' and PR.AUDIT_STS in ('4','5','7') and PR.BRANCH_CODE in ('1010') and PR.RESULT_MONTH = '201012' union<!--非团队成员小计-->select 2 ordersn, 1 orderno, '非团队成员' as team_Name,null userId,'小 计' userName, null brokerLevel, '' adjustDraftLevel, '' draftLevel, '' adjustDraftResult, '' draftResult , '' ifReport, '' detailRemark, PR.BRANCH_CODE branchCode, to_char( nvl(sum(to_char(pd.capital,'99999999999999990.99')),0)) capital, to_char( nvl(sum(to_char(pd.capital_daily,'99999999999999990.99')),0)) capitalDaily, to_char( nvl(sum(to_char( pd.tran_money_total,'99999999999999990.99')),0)) tranMoneyTotal, to_char(nvl(sum(to_char(pd.actual_fee_total,'99999999999999990.99')),0)) actualFeeTotal, to_char( nvl(sum(to_char(pd.net_fee_total,'99999999999999990.99')),0)) netFeeTotal, to_char( nvl(sum(to_char(pd.net_fee_convert_total,'99999999999999990.99')),0)) netFeeConvertTotal, to_char( nvl(sum(to_char( pd.net_fee_sum,'99999999999999990.99')),0)) netFeeSum, to_char( nvl(sum(to_char( pd.net_fee_monthly,'99999999999999990.99')),0)) netFeeMonthly, to_char( nvl(sum(to_char( pd.valid_cust,'99999999999999990.99')),0)) validCust, to_char(nvl(sum(to_char( pd.net_valid_cust ,'99999999999999990.99')),0)) netValidCust, to_char( nvl(sum(to_char( pd.net_valid_cust_convert,'99999999999999990.99')),0)) netValidCustConvert, to_char(nvl(sum(to_char(pd.net_valid_cust_sum,'99999999999999990.99')),0)) netValidCustSum, to_char( nvl(sum(to_char( pd.net_valid_cust_monthly,'99999999999999990.99')),0)) netValidCustMonthly from kh.tb_snap_user u inner join kh.tb_kh_personal_detail pd on pd.user_id=u.user_id inner join KH.TB_KH_PERSONAL_RESULT PR on PR.P_RESULT_ID=pd.P_RESULT_ID and PR.status = '1' left join ( select utr.user_id from kh.tb_snap_user_team_rel utr inner join kh.tb_snap_user_team ut on utr.team_id = ut.team_id and ut.team_sts = 0 where utr.status = 0) utr on u.user_id = utr.user_id where utr.user_id is null and u.user_type=0 and u.cur_state=0 and pd.IF_REPORT='1' and PR.AUDIT_STS in ('4','5','7') and PR.BRANCH_CODE in ('1010') and PR.RESULT_MONTH = '201012'group by PR.BRANCH_CODE, null union<!--业务部--> select 3 ordersn, 0 orderno, '业务部' as team_Name, u.user_id userId, u.username userName, pd.START_LEVEL brokerLevel, PADJ.adjusted_level adjustDraftLevel, pd.draft_level draftLevel, PADJ.adjusted_result adjustDraftResult, pd.draft_result draftResult, pd.if_report ifReport, PADJ.ADJUSTED_REMARK detailRemark, PR.BRANCH_CODE branchCode, to_char(nvl((pd.capital),0),'99999999999999990.99') capital, to_char(nvl((pd.capital_daily),0),'99999999999999990.99') capitalDaily, to_char(nvl(( pd.tran_money_total),0),'99999999999999990.99') tranMoneyTotal, to_char(nvl((pd.actual_fee_total),0),'99999999999999990.99') actualFeeTotal, to_char(nvl((pd.net_fee_total),0),'99999999999999990.99') netFeeTotal, to_char(nvl((pd.net_fee_convert_total),0),'99999999999999990.99') netFeeConvertTotal, to_char(nvl(( pd.net_fee_sum),0),'99999999999999990.99') netFeeSum, to_char(nvl(( pd.net_fee_monthly),0),'99999999999999990.99') netFeeMonthly, to_char(nvl(( pd.valid_cust),0),'99999999999999990.99') validCust, to_char(nvl(( pd.net_valid_cust),0),'99999999999999990.99') netValidCust, to_char(nvl(( pd.net_valid_cust_convert),0),'99999999999999990.99') netValidCustConvert, to_char(nvl((pd.net_valid_cust_sum),0),'99999999999999990.99') netValidCustSum, to_char(nvl(( pd.net_valid_cust_monthly),0),'99999999999999990.99') netValidCustMonthly from kh.tb_snap_user u inner join kh.tb_kh_personal_detail pd on pd.user_id=u.user_id left join KH.TB_KH_PERSONAL_ADJUSTED PADJ on pd.p_detail_id=PADJ.p_detail_id inner join KH.TB_KH_PERSONAL_RESULT PR on PR.P_RESULT_ID=pd.P_RESULT_ID and PR.status = '1' where u.user_type=4 and u.cur_state=0 and pd.IF_REPORT='1' and PR.AUDIT_STS in ('4','5','7') and PR.BRANCH_CODE in ('1010') and PR.RESULT_MONTH = '201012'union<!--业务部小计-->select 3 ordersn, 1 orderno, '业务部' as team_Name,null userId,'小 计' userName, null brokerLevel, '' adjustDraftLevel, '' draftLevel, '' adjustDraftResult, '' draftResult , '' ifReport, '' detailRemark, PR.BRANCH_CODE branchCode, to_char(nvl(sum(to_char(pd.capital,'99999999999999990.99')),0)) capital, to_char(nvl(sum(to_char(pd.capital_daily,'99999999999999990.99')),0)) capitalDaily, to_char(nvl(sum(to_char( pd.tran_money_total,'99999999999999990.99')),0)) tranMoneyTotal, to_char(nvl(sum(to_char(pd.actual_fee_total,'99999999999999990.99')),0)) actualFeeTotal, to_char(nvl(sum(to_char(pd.net_fee_total,'99999999999999990.99')),0)) netFeeTotal, to_char(nvl(sum(to_char(pd.net_fee_convert_total,'99999999999999990.99')),0)) netFeeConvertTotal, to_char(nvl(sum(to_char( pd.net_fee_sum,'99999999999999990.99')),0)) netFeeSum, to_char(nvl(sum(to_char( pd.net_fee_monthly,'99999999999999990.99')),0)) netFeeMonthly, to_char(nvl(sum(to_char( pd.valid_cust,'99999999999999990.99')),0)) validCust, to_char(nvl(sum(to_char( pd.net_valid_cust ,'99999999999999990.99')),0)) netValidCust, to_char(nvl(sum(to_char( pd.net_valid_cust_convert,'99999999999999990.99')),0)) netValidCustConvert, to_char(nvl(sum(to_char(pd.net_valid_cust_sum,'99999999999999990.99')),0)) netValidCustSum, to_char(nvl(sum(to_char( pd.net_valid_cust_monthly,'99999999999999990.99')),0)) netValidCustMonthly from kh.tb_snap_user u inner join kh.tb_kh_personal_detail pd on pd.user_id=u.user_id inner join KH.TB_KH_PERSONAL_RESULT PR on PR.P_RESULT_ID=pd.P_RESULT_ID and PR.status = '1' where u.user_type=4 and u.cur_state=0 and pd.IF_REPORT='1' and PR.AUDIT_STS in ('4','5','7') and PR.BRANCH_CODE in ('1010') and PR.RESULT_MONTH = '201012' group by PR.BRANCH_CODE, null union <!--总合 计-->select 4 ordersn, 0 orderno, '总合计' as team_Name, null userId ,'' username, null brokerLevel, '' adjustDraftLevel, '' draftLevel, '' adjustDraftResult, '' draftResult , '' ifReport, '' detailRemark, to_char(u.dept_id) branchCode, to_char(nvl(sum(to_char(capital,'99999999999999990.99')),0)) capital, to_char(nvl(sum(to_char(pd.capital_daily,'99999999999999990.99')),0)) capitalDaily, to_char(nvl(sum(to_char( pd.tran_money_total,'99999999999999990.99')),0)) tranMoneyTotal, to_char(nvl(sum(to_char(pd.actual_fee_total,'99999999999999990.99')),0)) actualFeeTotal, to_char(nvl(sum(to_char(pd.net_fee_total,'99999999999999990.99')),0)) netFeeTotal, to_char(nvl(sum(to_char(pd.net_fee_convert_total,'99999999999999990.99')),0)) netFeeConvertTotal, to_char(nvl(sum(to_char( pd.net_fee_sum,'99999999999999990.99')),0)) netFeeSum, to_char(nvl(sum(to_char( pd.net_fee_monthly,'99999999999999990.99')),0)) netFeeMonthly, to_char(nvl(sum(to_char( pd.valid_cust,'99999999999999990.99')),0)) validCust, to_char(nvl(sum(to_char( pd.net_valid_cust ,'99999999999999990.99')),0)) netValidCust, to_char(nvl(sum(to_char( pd.net_valid_cust_convert,'99999999999999990.99')),0)) netValidCustConvert, to_char(nvl(sum(to_char(pd.net_valid_cust_sum,'99999999999999990.99')),0)) netValidCustSum, to_char(nvl(sum(to_char( pd.net_valid_cust_monthly,'99999999999999990.99')),0)) netValidCustMonthly from kh.tb_snap_user u inner join kh.tb_kh_personal_detail pd on pd.user_id=u.user_id inner join KH.TB_KH_PERSONAL_RESULT PR on PR.P_RESULT_ID=pd.P_RESULT_ID and PR.status = '1' where u.cur_state=0 and pd.IF_REPORT='1' and PR.AUDIT_STS in ('4','5','7') and PR.BRANCH_CODE in ('1010') and PR.RESULT_MONTH = '201012' group by u.dept_id ,null) order by ordersn,team_name,orderno ?
?