回答編集履歴
1
訂正
test
CHANGED
|
@@ -42,6 +42,40 @@
|
|
|
42
42
|
|
|
43
43
|
２．join用に結合した文字列を[substring_index()](https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_substring-index)を使用して分解し属性を変換します。
|
|
44
44
|
|
|
45
|
+
追記
|
|
46
|
+
|
|
47
|
+
--
|
|
48
|
+
|
|
49
|
+
桁が固定にでき、文字位置で判断できるので、単純に結合した方がシンプルでしたので訂正。
|
|
50
|
+
|
|
51
|
+
```SQL
|
|
52
|
+
|
|
53
|
+
select no1
|
|
54
|
+
|
|
55
|
+
, cast(substring(concat_no, 1, 3) as tinyint) as no2
|
|
56
|
+
|
|
57
|
+
, cast(substring(concat_no, 4, 3) as tinyint) as no3
|
|
58
|
+
|
|
59
|
+
, cast(substring(concat_no, 7, 3) as tinyint) as no4
|
|
60
|
+
|
|
61
|
+
from (
|
|
62
|
+
|
|
63
|
+
select no1
|
|
64
|
+
|
|
65
|
+
, max(concat(
|
|
66
|
+
|
|
67
|
+
lpad(cast(no2 as char), 3, '0'), lpad(cast(no3 as char), 3, '0'),lpad(cast(no4 as char), 3, '0')
|
|
68
|
+
|
|
69
|
+
)) as concat_no
|
|
70
|
+
|
|
71
|
+
from keiyaku_detail_table
|
|
72
|
+
|
|
73
|
+
group by no1
|
|
74
|
+
|
|
75
|
+
) valid_key
|
|
76
|
+
|
|
77
|
+
```
|
|
78
|
+
|
|
45
79
|
|
|
46
80
|
|
|
47
81
|
上記を使用して以下の様なSQLになります。
|
|
@@ -76,25 +110,21 @@
|
|
|
76
110
|
|
|
77
111
|
select no1
|
|
78
112
|
|
|
79
|
-
, cast(substring
|
|
113
|
+
, cast(substring(concat_no, 1, 3) as tinyint) as no2
|
|
80
114
|
|
|
81
|
-
, cast(substring
|
|
115
|
+
, cast(substring(concat_no, 4, 3) as tinyint) as no3
|
|
82
116
|
|
|
83
|
-
, cast(substring
|
|
117
|
+
, cast(substring(concat_no, 7, 3) as tinyint) as no4
|
|
84
118
|
|
|
85
119
|
from (
|
|
86
120
|
|
|
87
|
-
select no1
|
|
121
|
+
select no1
|
|
88
122
|
|
|
89
|
-
, max(concat(
|
|
123
|
+
, max(concat(
|
|
90
124
|
|
|
91
|
-
lpad(cast(no
|
|
125
|
+
lpad(cast(no2 as char), 3, '0'), lpad(cast(no3 as char), 3, '0'),lpad(cast(no4 as char), 3, '0')
|
|
92
126
|
|
|
93
|
-
, '-', lpad(cast(no3 as char), 3, '0')
|
|
94
|
-
|
|
95
|
-
, '-' ,lpad(cast(no4 as char), 3, '0')
|
|
96
|
-
|
|
97
|
-
)) as concat_no
|
|
127
|
+
)) as concat_no
|
|
98
128
|
|
|
99
129
|
from keiyaku_detail_table
|
|
100
130
|
|