先写了一个查询语句 WITH T1 AS( SELECT A,B,SUM(A/B)OVER(ORDER BY 排序方式) RC FROM T ),T2 AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM T) ) SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD(V+T2.RN,4,'0'))) FROM T1,T2 WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN
设定你的主键为ID,排序字段为S update T SET C=(SELECT C FROM ( WITH T1 AS( SELECT ID,A,B,SUM(A/B)OVER(ORDER BY S) RC FROM T ),T2 AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM T) ) SELECT T1.ID,TO_CHAR(WMSYS.WM_CONCAT(LPAD(V+T2.RN,4,'0'))) AS C FROM T1,T2 WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN )WHERE ID=T.ID )
WITH T1 AS( SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC FROM temp_A ),T2 AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A) )SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0'))) FROM T1,T2 WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN这边报“不是单组分组函数”?
WITH T1 AS( SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC FROM temp_A ),T2 AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A) ) SELECT MAX(T1.A),MAX(T1.B),TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0'))) FROM T1,T2 WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN GROUP BY T1.ROWID
WITH T1 AS( SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC FROM temp_A ),T2 AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A) )SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0'))) FROM T1,T2 WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN这边报“不是单组分组函数”?
太牛逼了,谢谢大神,请受小弟一拜
WITH T1 AS( SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC FROM temp_A ),T2 AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A) )SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0'))) FROM T1,T2 WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN这边报“不是单组分组函数”? 大神能否留个联系方式,不是问你问题,不会缠你,交个朋友,我qq315235277
WITH T1 AS(
SELECT A,B,SUM(A/B)OVER(ORDER BY 排序方式) RC
FROM T
),T2 AS (
SELECT ROWNUM RN FROM DUAL
CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM T)
)
SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD(V+T2.RN,4,'0')))
FROM T1,T2
WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN
update T
SET C=(SELECT C FROM (
WITH T1 AS(
SELECT ID,A,B,SUM(A/B)OVER(ORDER BY S) RC
FROM T
),T2 AS (
SELECT ROWNUM RN FROM DUAL
CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM T)
)
SELECT T1.ID,TO_CHAR(WMSYS.WM_CONCAT(LPAD(V+T2.RN,4,'0'))) AS C
FROM T1,T2
WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN
)WHERE ID=T.ID
)
WITH T1 AS(
SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC
FROM temp_A
),T2 AS (
SELECT ROWNUM RN FROM DUAL
CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A)
)SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0')))
FROM T1,T2
WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN这边报“不是单组分组函数”?
SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC
FROM temp_A
),T2 AS (
SELECT ROWNUM RN FROM DUAL
CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A)
)
SELECT MAX(T1.A),MAX(T1.B),TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0')))
FROM T1,T2
WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN
GROUP BY T1.ROWID
WITH T1 AS(
SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC
FROM temp_A
),T2 AS (
SELECT ROWNUM RN FROM DUAL
CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A)
)SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0')))
FROM T1,T2
WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN这边报“不是单组分组函数”?
太牛逼了,谢谢大神,请受小弟一拜
WITH T1 AS(
SELECT A,B,SUM(A/B)OVER(ORDER BY A,B) RC
FROM temp_A
),T2 AS (
SELECT ROWNUM RN FROM DUAL
CONNECT BY ROWNUM<=(SELECT SUM(A/B) FROM temp_A)
)SELECT T1.A,T1.B,TO_CHAR(WMSYS.WM_CONCAT(LPAD('0001'+T2.RN,4,'0')))
FROM T1,T2
WHERE T1.RC>=T2.RN AND T1.RC-T1.A/T1.B<T2.RN这边报“不是单组分组函数”?
大神能否留个联系方式,不是问你问题,不会缠你,交个朋友,我qq315235277