oracle 相邻行某两字段相同的行生成同一个单号

[Miracle_lucy]

oracle 相邻行某两字段相同的行生成同一个单号

如下图为公司和日期都相同的相邻行，生成一个单号，单号最好用日期组成201411001,201411002,201411003

求sql脚本

[桃花岛黄岛主]

with t as (
select 'aaaaa' a, 1 b from dual union all
select 'aaaaa' a, 1 b from dual union all
select 'aaaaa' a, 2 b from dual union all
select 'aaaaa' a, 2 b from dual union all
select 'bbbbb' a, 1 b from dual union all
select 'bbbbb' a, 2 b from dual union all
select 'bbbbb' a, 1 b from dual union all
select 'ccccc' a, 2 b from dual union all
select 'ccccc' a, 2 b from dual union all
select 'd' a, 2 b from dual
) 
,
t1 as(
select 
rank() over (order by a ) pid1,
rank() over (partition by a order by b ) pid,
t.* from t)

select to_char(sysdate,'yyyymmdd')||lpad(pid1,3,'0')||lpad(pid,3,'0') pid,
a,
b
 from t1

试试看

[chengccy]

with tmp as (select 'A' as dept , date '2014-11-25' as t_date from dual union all select 'A' as dept , date '2014-11-25' as t_date from dual union all select 'A' as dept , date '2014-11-25' as t_date from dual union all select 'A' as dept , date '2014-11-13' as t_date from dual union all select 'A' as dept , date '2014-11-13' as t_date from dual union all select 'A' as dept , date '2014-11-13' as t_date from dual union all select 'B' as dept , date '2014-11-13' as t_date from dual union all select 'B' as dept , date '2014-11-13' as t_date from dual union all select 'B' as dept , date '2014-11-13' as t_date from dual) SELECT DEPT, T_DATE, TO_CHAR(T_DATE, 'YYYYMM') || LPAD(SUM(RN) OVER(ORDER BY ROWNUM), 3, '0') AS "单号" FROM (SELECT DEPT, T_DATE, CASE WHEN LAG(DEPT) OVER(ORDER BY ROWNUM) = DEPT AND LAG(T_DATE) OVER(ORDER BY ROWNUM) = T_DATE THEN 0 ELSE 1 END RN FROM TMP T) 需要排序的话,把rownum 改成排序字段

[js14982]

需要用到序列么？ 一般单号都用序列来增长。 update test a set id=to_char(rq,'yyyymm')||lpad(nextval.suq_name,3,0) where (com,rq)=(select com,rq from test where com=a.com and rq=a.rq); 大概是这么个逻辑吧

[桃花岛黄岛主]

引用 1 楼 wang123kui 的回复:

with t as (
select 'aaaaa' a, 1 b from dual union all
select 'aaaaa' a, 1 b from dual union all
select 'aaaaa' a, 2 b from dual union all
select 'aaaaa' a, 2 b from dual union all
select 'bbbbb' a, 1 b from dual union all
select 'bbbbb' a, 2 b from dual union all
select 'bbbbb' a, 1 b from dual union all
select 'ccccc' a, 2 b from dual union all
select 'ccccc' a, 2 b from dual union all
select 'd' a, 2 b from dual
) 
,
t1 as(
select 
rank() over (order by a ) pid1,
rank() over (partition by a order by b ) pid,
t.* from t)

select to_char(sysdate,'yyyymmdd')||lpad(pid1,3,'0')||lpad(pid,3,'0') pid,
a,
b
 from t1

试试看

用dense_rank可能会更好点