回答編集履歴
1
訂正
answer
CHANGED
|
@@ -20,6 +20,23 @@
|
|
|
20
20
|
解説：
|
|
21
21
|
１．na2～3を桁を揃えて結合し、no1に対する最大を求めます。※後で分解する為`-`で繋ぎます。
|
|
22
22
|
２．join用に結合した文字列を[substring_index()](https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_substring-index)を使用して分解し属性を変換します。
|
|
23
|
+
追記
|
|
24
|
+
--
|
|
25
|
+
桁が固定にでき、文字位置で判断できるので、単純に結合した方がシンプルでしたので訂正。
|
|
26
|
+
```SQL
|
|
27
|
+
select no1
|
|
28
|
+
, cast(substring(concat_no, 1, 3) as tinyint) as no2
|
|
29
|
+
, cast(substring(concat_no, 4, 3) as tinyint) as no3
|
|
30
|
+
, cast(substring(concat_no, 7, 3) as tinyint) as no4
|
|
31
|
+
from (
|
|
32
|
+
select no1
|
|
33
|
+
, max(concat(
|
|
34
|
+
lpad(cast(no2 as char), 3, '0'), lpad(cast(no3 as char), 3, '0'),lpad(cast(no4 as char), 3, '0')
|
|
35
|
+
)) as concat_no
|
|
36
|
+
from keiyaku_detail_table
|
|
37
|
+
group by no1
|
|
38
|
+
) valid_key
|
|
39
|
+
```
|
|
23
40
|
|
|
24
41
|
上記を使用して以下の様なSQLになります。
|
|
25
42
|
※手打ちで動作検証はしていませんので、悪しからず。
|
|
@@ -37,16 +54,14 @@
|
|
|
37
54
|
and dtl.no4 = kyk.no4
|
|
38
55
|
inner join (
|
|
39
56
|
select no1
|
|
40
|
-
, cast(
|
|
57
|
+
, cast(substring(concat_no, 1, 3) as tinyint) as no2
|
|
41
|
-
, cast(
|
|
58
|
+
, cast(substring(concat_no, 4, 3) as tinyint) as no3
|
|
42
|
-
, cast(
|
|
59
|
+
, cast(substring(concat_no, 7, 3) as tinyint) as no4
|
|
43
60
|
from (
|
|
44
|
-
select
|
|
61
|
+
select no1
|
|
45
|
-
|
|
62
|
+
, max(concat(
|
|
46
|
-
lpad(cast(no2 as char), 3, '0')
|
|
47
|
-
|
|
63
|
+
lpad(cast(no2 as char), 3, '0'), lpad(cast(no3 as char), 3, '0'),lpad(cast(no4 as char), 3, '0')
|
|
48
|
-
, '-' ,lpad(cast(no4 as char), 3, '0')
|
|
49
|
-
|
|
64
|
+
)) as concat_no
|
|
50
65
|
from keiyaku_detail_table
|
|
51
66
|
group by no1
|
|
52
67
|
) valid_key
|