以前做了一个统计。基本语句是
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这个统计出来排序乱了,总计在小计的上面,不晓得问题出在哪里了。请教高手了。
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这个统计出来排序乱了,总计在小计的上面,不晓得问题出在哪里了。请教高手了。
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
)
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;
显示结果是:
1 111 aaa 代理 1
2 111 aaa 其他 2
3 操作员小计 3
4 商户小计 22 4
5 222 bbb 其他 5
6 222 bbb 代理 6
7 操作员小计 7
8 商户小计 98 8改后:
with tab as(
select ttt.*,tt.cnum 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
)
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;结果为:1 111 aaa 其他 1
2 111 aaa 代理 2
3 商户小计 98 3
4 操作员小计 4
5 商户小计 22 5
6 操作员小计 6
7 222 bbb 代理 7
8 222 bbb 其他 8显示乱了。不知如何处理
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;
我只能是再套一层select ,解决了这个问题
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;