发票在出票时,有票号的,并且不会重复,表结构:
rq(日期) 操作类型 kshm(开始号码) jshm(结束号码) sl(数量)
2010-1-1 出库 000001 000100 100
2010-1-1 出库 000101 000200 100
2010-1-1 出库 000401 000600 600
2010-1-1 入库 100001 100100 100
2010-1-1 出库 000701 000800 800现在需要把号码合并,当然数量比较多.结果为rq(日期) 操作类型 kshm(开始号码) jshm(结束号码) sl(数量)
2010-1-1 出库 000001 000200 200
2010-1-1 出库 000401 000600 600
2010-1-1 入库 100001 100100 100
2010-1-1 出库 000701 000800 800
我想用每条记录kshm,jshm,循环成每个号码,例如:rq(日期) 操作类型 kshm 数量
2010-1-1 出库 000001 1
2010-1-1 出库 000002 1
2010-1-1 出库 000003 1
2010-1-1 出库 000004 1
..............................
然后进行合并, select to_char(b.rq),czlx,pj_id,min(b.pjhm),max(b.pjhm) from (
select a.*,TO_NUMBER(a.pjhm - ROWNUM) cc from(
select pj_id,pjhm,rq from kjjks_title where pj_id ='121' order by pjhm)a
)
b
group by b.pj_id,b.cc,b.rq
order by b.rq
可以实现,但太麻烦了,效率低,各位高手,有什么好的办法??给100分,够吗??
rq(日期) 操作类型 kshm(开始号码) jshm(结束号码) sl(数量)
2010-1-1 出库 000001 000100 100
2010-1-1 出库 000101 000200 100
2010-1-1 出库 000401 000600 600
2010-1-1 入库 100001 100100 100
2010-1-1 出库 000701 000800 800现在需要把号码合并,当然数量比较多.结果为rq(日期) 操作类型 kshm(开始号码) jshm(结束号码) sl(数量)
2010-1-1 出库 000001 000200 200
2010-1-1 出库 000401 000600 600
2010-1-1 入库 100001 100100 100
2010-1-1 出库 000701 000800 800
我想用每条记录kshm,jshm,循环成每个号码,例如:rq(日期) 操作类型 kshm 数量
2010-1-1 出库 000001 1
2010-1-1 出库 000002 1
2010-1-1 出库 000003 1
2010-1-1 出库 000004 1
..............................
然后进行合并, select to_char(b.rq),czlx,pj_id,min(b.pjhm),max(b.pjhm) from (
select a.*,TO_NUMBER(a.pjhm - ROWNUM) cc from(
select pj_id,pjhm,rq from kjjks_title where pj_id ='121' order by pjhm)a
)
b
group by b.pj_id,b.cc,b.rq
order by b.rq
可以实现,但太麻烦了,效率低,各位高手,有什么好的办法??给100分,够吗??
from kjjks_title t
start with not exists
(select 1
from kjjks_title
where rq = t.rq
and czlx = t.czlx
and jshm = to_char(to_number(t.kshm) - 1, 'fm000000'))
connect by prior rq = rq
and prior czlx = czlx
and prior to_char(to_number(jshm) + 1, 'fm000000') = kahm
group by rownum - level, rq, czlx
with test as(
select date'2010-1-1' rq,'出库' optype, '000001' kshm, '000100' jshm, 100 sl from dual union
select date'2010-1-1' rq,'出库' optype, '000101' kshm, '000200' jshm, 100 sl from dual union
select date'2010-1-1' rq,'出库' optype, '000401' kshm, '000600' jshm, 600 sl from dual union
select date'2010-1-1' rq,'入库' optype, '100001' kshm, '100100' jshm, 100 sl from dual union
select date'2010-1-1' rq,'出库' optype, '000701' kshm, '000800' jshm, 800 sl from dual)
SELECT rq, optype, MIN(kshm) kshm, MAX(jshm), SUM(sl)
FROM test t
START WITH NOT EXISTS (SELECT 1 FROM test WHERE jshm = t.kshm - 1)
CONNECT BY PRIOR jshm = kshm - 1 AND
PRIOR rq = rq AND
PRIOR optype = optype
GROUP BY rq, optype, rownum - LEVEL;
--如果号码可跨天使用的话,就不要按rq字段分组了
要不然,好像报错,
改成:Connect By nocycle
with test as(
select date'2010-1-1' rq,'出库' optype, '000001' kshm, '000100' jshm, 100 sl from dual union
select date'2010-1-1' rq,'出库' optype, '000101' kshm, '000200' jshm, 100 sl from dual union
select date'2010-1-1' rq,'出库' optype, '000401' kshm, '000600' jshm, 600 sl from dual union
select date'2010-1-1' rq,'入库' optype, '100001' kshm, '100100' jshm, 100 sl from dual union
select date'2010-1-1' rq,'出库' optype, '000701' kshm, '000800' jshm, 800 sl from dual),
t AS
(SELECT rq,optype, kshm, jshm, sl,lag(jshm, 1, 0) over(PARTITION BY rq,optype ORDER BY kshm) prev_jshm
FROM test)
SELECT *
FROM (SELECT rq, optype, MIN(kshm) kshm, MAX(jshm) jshm, SUM(sl)
FROM t
WHERE kshm = prev_jshm + 1
GROUP BY rq, optype
UNION ALL
SELECT rq, optype, kshm, jshm, sl FROM t WHERE kshm <> prev_jshm + 1)
ORDER BY rq, optype;
??你试了会报错
kahm笔误,是kshm
prior to_char(to_number(jshm) + 1, 'fm000000') = kshm
都会出现循环的话,就是数据有问题了