回答編集履歴
5
コード修正
test
CHANGED
|
@@ -28,13 +28,11 @@
|
|
|
28
28
|
|
|
29
29
|
PostgreSQLで以下の感じのクエリを書くと実現出来そうなんですが･･･
|
|
30
30
|
|
|
31
|
-
|
|
31
|
+
** ※クエリが盛大に間違ってたので修正 **
|
|
32
|
-
|
|
33
|
-
|
|
34
32
|
|
|
35
33
|
```SQL
|
|
36
34
|
|
|
37
|
-
UPDATE table
|
|
35
|
+
UPDATE table
|
|
38
36
|
|
|
39
37
|
SET
|
|
40
38
|
|
|
@@ -46,7 +44,9 @@
|
|
|
46
44
|
|
|
47
45
|
SELECT
|
|
48
46
|
|
|
49
|
-
T
|
|
47
|
+
T2.*
|
|
48
|
+
|
|
49
|
+
, T3.LETTER
|
|
50
50
|
|
|
51
51
|
FROM
|
|
52
52
|
|
|
@@ -56,9 +56,9 @@
|
|
|
56
56
|
|
|
57
57
|
T1.MIN_ID
|
|
58
58
|
|
|
59
|
-
, T1.UPD_KEY
|
|
59
|
+
, T1.UPD_KEY
|
|
60
60
|
|
|
61
|
-
,
|
|
61
|
+
, ROW_NUMBER() OVER (ORDER BY MIN_ID) - 1 AS ROW_NUM
|
|
62
62
|
|
|
63
63
|
FROM
|
|
64
64
|
|
|
@@ -68,29 +68,23 @@
|
|
|
68
68
|
|
|
69
69
|
MIN(ID1) AS MIN_ID
|
|
70
70
|
|
|
71
|
-
, ID2 AS UPD_KEY
|
|
71
|
+
, ID2 AS UPD_KEY
|
|
72
|
-
|
|
73
|
-
, ROW_NUMBER() OVER (ORDER BY ID2) - 1 AS ROW_NUM
|
|
74
72
|
|
|
75
73
|
FROM
|
|
76
74
|
|
|
77
|
-
table
|
|
75
|
+
table
|
|
78
76
|
|
|
79
77
|
GROUP BY
|
|
80
78
|
|
|
81
79
|
ID2
|
|
82
80
|
|
|
83
|
-
|
|
81
|
+
) T1
|
|
84
82
|
|
|
85
|
-
|
|
83
|
+
) T2
|
|
86
84
|
|
|
87
|
-
|
|
85
|
+
INNER JOIN LETTERS T3
|
|
88
86
|
|
|
89
|
-
INNER JOIN LETTERS T2
|
|
90
|
-
|
|
91
|
-
|
|
87
|
+
ON MOD(T2.ROW_NUM, 25) = T3.SEQNO
|
|
92
|
-
|
|
93
|
-
) T3
|
|
94
88
|
|
|
95
89
|
) T4
|
|
96
90
|
|
4
コード修正
test
CHANGED
|
@@ -56,7 +56,7 @@
|
|
|
56
56
|
|
|
57
57
|
T1.MIN_ID
|
|
58
58
|
|
|
59
|
-
, T1.
|
|
59
|
+
, T1.UPD_KEY
|
|
60
60
|
|
|
61
61
|
, T2.LETTER
|
|
62
62
|
|
|
@@ -66,9 +66,9 @@
|
|
|
66
66
|
|
|
67
67
|
SELECT
|
|
68
68
|
|
|
69
|
-
MIN(ID1) MIN_ID
|
|
69
|
+
MIN(ID1) AS MIN_ID
|
|
70
70
|
|
|
71
|
-
, ID2
|
|
71
|
+
, ID2 AS UPD_KEY
|
|
72
72
|
|
|
73
73
|
, ROW_NUMBER() OVER (ORDER BY ID2) - 1 AS ROW_NUM
|
|
74
74
|
|
3
誤字修正
test
CHANGED
|
@@ -114,6 +114,6 @@
|
|
|
114
114
|
|
|
115
115
|
**・追記**
|
|
116
116
|
|
|
117
|
-
MOD(
|
|
117
|
+
MOD(T1.ROW_NUM, 25)と文字列変換テーブルの連番で結合かけてるので、
|
|
118
118
|
|
|
119
119
|
zまで採番が終わるとaから循環し直す仕様にしてます。
|
2
コード変更
test
CHANGED
|
@@ -70,7 +70,7 @@
|
|
|
70
70
|
|
|
71
71
|
, ID2
|
|
72
72
|
|
|
73
|
-
,
|
|
73
|
+
, ROW_NUMBER() OVER (ORDER BY ID2) - 1 AS ROW_NUM
|
|
74
74
|
|
|
75
75
|
FROM
|
|
76
76
|
|
1
追記
test
CHANGED
|
@@ -88,7 +88,7 @@
|
|
|
88
88
|
|
|
89
89
|
INNER JOIN LETTERS T2
|
|
90
90
|
|
|
91
|
-
ON
|
|
91
|
+
ON MOD(T1.ROW_NUM, 25) = T2.SEQNO
|
|
92
92
|
|
|
93
93
|
) T3
|
|
94
94
|
|
|
@@ -105,3 +105,15 @@
|
|
|
105
105
|
これをMySQL風に書き直そうと思いましたが力尽きました･･･
|
|
106
106
|
|
|
107
107
|
(MySQLはROW_NUMBER分析関数はサポートしてないのね･･･orz)
|
|
108
|
+
|
|
109
|
+
|
|
110
|
+
|
|
111
|
+
|
|
112
|
+
|
|
113
|
+
|
|
114
|
+
|
|
115
|
+
**・追記**
|
|
116
|
+
|
|
117
|
+
MOD(MOD(T1.ROW_NUM, 25))と文字列変換テーブルの連番で結合かけてるので、
|
|
118
|
+
|
|
119
|
+
zまで採番が終わるとaから循環し直す仕様にしてます。
|