有表数据如下:
AUTOID AA BB CC
1 qwe 11 cc
2 fda 11 cc
3 ccd 11 cc
4 fde 11 cc
5 pow 11 cc
6 ytr 11 bb
7 uyt 22 cc
8 oiu 22 cc
9 yti er cc
现在需要把表中每行BB字段和CC字段相等的值的行AA值更新为AUTOID最小的AA字段的值,且连续的行不超过3的倍数,超过的更新为第3*N+1的AA字段的值行,更新后,表的值如下:
AUTOID AA BB CC
1 qwe 11 cc
2 qwe 11 cc
3 qwe 11 cc
4 fde 11 cc
5 fde 11 cc
6 ytr 11 bb
7 uyt 22 cc
8 uyt 22 cc
9 yti er cc
请问SQL语句怎么写
AUTOID AA BB CC
1 qwe 11 cc
2 fda 11 cc
3 ccd 11 cc
4 fde 11 cc
5 pow 11 cc
6 ytr 11 bb
7 uyt 22 cc
8 oiu 22 cc
9 yti er cc
现在需要把表中每行BB字段和CC字段相等的值的行AA值更新为AUTOID最小的AA字段的值,且连续的行不超过3的倍数,超过的更新为第3*N+1的AA字段的值行,更新后,表的值如下:
AUTOID AA BB CC
1 qwe 11 cc
2 qwe 11 cc
3 qwe 11 cc
4 fde 11 cc
5 fde 11 cc
6 ytr 11 bb
7 uyt 22 cc
8 uyt 22 cc
9 yti er cc
请问SQL语句怎么写
--先把结果查询出来,放到临时表,之后再更新
--查询sql
[SYS@myoracle] SQL>WITH T1 AS(
2 SELECT 1 AUTOID,'qwe' AA,'11' BB,'cc' CC FROM DUAL UNION ALL
3 SELECT 2 AUTOID,'fda' AA,'11' BB,'cc' CC FROM DUAL UNION ALL
4 SELECT 3 AUTOID,'ccd' AA,'11' BB,'cc' CC FROM DUAL UNION ALL
5 SELECT 4 AUTOID,'fde' AA,'11' BB,'cc' CC FROM DUAL UNION ALL
6 SELECT 5 AUTOID,'pow' AA,'11' BB,'cc' CC FROM DUAL UNION ALL
7 SELECT 6 AUTOID,'ytr' AA,'11' BB,'bb' CC FROM DUAL UNION ALL
8 SELECT 7 AUTOID,'uyt' AA,'22' BB,'cc' CC FROM DUAL UNION ALL
9 SELECT 8 AUTOID,'oiu' AA,'22' BB,'cc' CC FROM DUAL UNION ALL
10 SELECT 9 AUTOID,'yti' AA,'er' BB,'cc' CC FROM DUAL
11 )SELECT AUTOID,
12 MAX(AA) KEEP(DENSE_RANK FIRST ORDER BY AUTOID) OVER(PARTITION BY BB, CC, RN) AA,
13 BB,
14 CC
15 FROM (SELECT AUTOID,
16 AA,
17 BB,
18 CC,
19 CEIL(ROW_NUMBER()
20 OVER(PARTITION BY BB, CC ORDER BY AUTOID) / 3) RN
21 FROM T1)
22 ORDER BY 1; AUTOID AA BB CC
---------- --- -- --
1 qwe 11 cc
2 qwe 11 cc
3 qwe 11 cc
4 fde 11 cc
5 fde 11 cc
6 ytr 11 bb
7 uyt 22 cc
8 uyt 22 cc
9 yti er cc已选择9行。