以前做了一个统计。基本语句是
with tab as()
select name,rown r1 from tab
order by rn1
排序是正确的。
数据
小计
总计
但是现在要加一个客户数的统计。
改成
with tab as( select ..... tt left join .....)
select name,rown r1 from tab
order by rn1
这个统计出来排序乱了，总计在小计的上面，不晓得问题出在哪里了。
请教高手了。
------解决方案--------------------
小计和总计数据是怎样统计出来的？
给出样例数据和结果示例。
------解决方案--------------------
- SQL code
with tab as(select ttt.*,tt.cnum from (select '111' id, 'aaa' name, '代理' type,1 order_id from dual union all select '111', 'aaa', '其他' ,2 from dual union all select '操作员小计', '', '18',3 from dual union all select '商户小计', '', '22' ,4 from dual union all select '222', 'bbb', '其他' ,5 from dual union all select '222', 'bbb', '代理' , 6 from dual union all select '操作员小计', '', '99' ,7 from dual union all select '商户小计', '', '98' , 8 from dual)ttt left join (select '2' as cnum,'aaa' as name from dual ) tt on ttt.name=tt.name ORDER BY order_id ) select id ,name,type,rn1 from ( select id,name ,type ,row_number()over(partition by id,name order by rownum) rn ,rownum rn1 from tab) order by rn1;
------解决方案--------------------
with tab as(
select ttt.*,tt.cnum from (
select id,name,type,rownum r1 from(
select '111' id, 'aaa' name, '代理' type from dual union all
select '111', 'aaa', '其他' from dual union all
select '操作员小计', '', '18' from dual union all
select '商户小计', '', '22' from dual union all
select '222', 'bbb', '其他' from dual union all
select '222', 'bbb', '代理' from dual union all
select '操作员小计', '', '99' from dual union all
select '商户小计', '', '98' from dual))ttt
left join
(select '2' as cnum,'aaa' as name from dual ) tt
on ttt.name=tt.name order by ttt.r1
)
select id ,name,type,rn1 from (
select id,name ,type ,row_number()over(partition by id,name order by rownum) rn ,rownum rn1 from tab)
order by rn1;