mysqlにて複数のカラムから1つのレコードを一括で抽出するクエリの作成

Question

前提・実現したいこと

mysqlにて複数のカラムから1つのレコードを一括で抽出するクエリの作成
mysqlバージョン : Ver 14.14 Distrib 5.7.25

発生している問題・エラーメッセージ

期待抽出結果のように取得するクエリを作成のため、お力添えいただけると助かります。
areaテーブルのarea_cd1とarea_cd2の組み合わせごとに、keiyaku_tableから下記条件に合致する最大値のレコードを取得し、keiyaku_detail_tableのkeiyaku_ypeを取得したいです。

・抽出条件 (同一のレコードが複数存在する場合、1)→2)→3)→4)の順でチェック)

1. 条件no1カラムの最大カラム
2. 条件no2カラムの最大カラム
3. 条件no3カラムの最大カラム
4. 条件no4カラムの最大カラム

・各カラム属性
area_cd1 : VARCHAR(10)
area_cd2 : VARCHAR(10)
no1 : int
no2 : tinyint
no3 : tinyint
no4 : tinyint
keiyaku_type : VARCHAR(10)

試したこと

下記クエリで1件ずつ取得はできたのですが、抽出したいレコードが1000件近くあるため、一括で取得できればと思っています。

SELECT area_table.area_cd1,area_table.area_cd2,keiyaku_detail_table.no1,keiyaku_detail_table.no2,keiyaku_detail_table.no3,keiyaku_detail_table.no4,keiyaku_detail_table.keiyaku_type 
FROM keiyaku_detail_table
INNER JOIN keiyaku_no_table ON (
 keiyaku_detail_table.no1 = keiyaku_no_table.no1 AND
 keiyaku_detail_table.no2 = keiyaku_no_table.no2 AND
 keiyaku_detail_table.no3 = keiyaku_no_table.no3 AND
 keiyaku_detail_table.no4 = keiyaku_no_table.no4)
WHERE (area_table.area_cd1 = '10' AND area_table.area_cd2 = '100')
ORDER BY 
keiyaku_detail_table.no1 DESC,
keiyaku_detail_table.no2 DESC,
keiyaku_detail_table.no3 DESC,
keiyaku_detail_table.no4 DESC
LIMIT 1;

Comments

[K_Y_M]

期待抽出結果3行目のレコードは
20 100 3300 2 1 1 M　ではなく
20 100 3300 1 1 1 M　なのでしょうか？

[sazi]

MｙSQLのバージョンは何ですか？質問に追記して下さい。

[sazi]

それから、no1～no4の属性は何ですか？
no1～no4を文字列として結合したものをソートしても希望する並びになりますか？

[Yuki_T]

K_Y_M 様
ご指摘恐れ入ります、ご指摘通りですので修正させていただきました。

sazi 様
バージョンと属性を追記させていただきました。
> no1～no4を文字列として結合したものをソートしても希望する並びになりますか？
はい、希望する並びとなります

Answer 1

keiyaku_detail_tableから最大値のキーを取得するSQLを考えてみます。
マルチカラムなので項目ごとに順次求めようとすると、それぞれをネストした集計する相関サブクエリーが必要です。
一塊のカラムに纏めるとネストが不要になるので、先ずはパフォーマンスは考慮せずにこの方針でSQLを組み立てると以下の様になります。

select no1
     , cast(substring_index(substring_index(concat_no, '-', 1), '_', -1) as tinyint) as no2
     , cast(substring_index(substring_index(concat_no, '-', 2), '_', -1) as tinyint) as no3
     , cast(substring_index(substring_index(concat_no, '-', 3), '_', -1) as tinyint) as no4
from  (
        select no1
             , max(concat(
                       lpad(cast(no2 as char), 3, '0')
                , '-', lpad(cast(no3 as char), 3, '0')
                , '-' ,lpad(cast(no4 as char), 3, '0')
               )) as concat_no
        from keiyaku_detail_table
        group by no1
      ) valid_key

解説：
１．na2～3を桁を揃えて結合し、no1に対する最大を求めます。※後で分解する為-で繋ぎます。
２．join用に結合した文字列をsubstring_index()を使用して分解し属性を変換します。
追記

桁が固定にでき、文字位置で判断できるので、単純に結合した方がシンプルでしたので訂正。

select no1
     , cast(substring(concat_no, 1, 3) as tinyint) as no2
     , cast(substring(concat_no, 4, 3) as tinyint) as no3
     , cast(substring(concat_no, 7, 3) as tinyint) as no4
from  (
        select  no1
              , max(concat(
                  lpad(cast(no2 as char), 3, '0'), lpad(cast(no3 as char), 3, '0'),lpad(cast(no4 as char), 3, '0')
                )) as concat_no
        from keiyaku_detail_table
        group by no1
      ) valid_key

上記を使用して以下の様なSQLになります。
※手打ちで動作検証はしていませんので、悪しからず。

select  area.area_cd1, area.area_cd2
      , dtl.no1, dtl.no2, dtl.no3, dtl.no4, dtl.keiyaku_type 
from  area_table area
      inner join keiyaku_no_table kyk
      on    kyk.area_cd1 = area.area_cd1
        and kyk.area_cd2 = area.area_cd2
      inner join keiyaku_detail_table dtl
      on    dtl.no1 = kyk.no1
        and dtl.no2 = kyk.no2
        and dtl.no3 = kyk.no3
        and dtl.no4 = kyk.no4
      inner join (
        select no1
             , cast(substring(concat_no, 1, 3) as tinyint) as no2
             , cast(substring(concat_no, 4, 3) as tinyint) as no3
             , cast(substring(concat_no, 7, 3) as tinyint) as no4
        from  (
                select  no1
                      , max(concat(
                          lpad(cast(no2 as char), 3, '0'), lpad(cast(no3 as char), 3, '0'),lpad(cast(no4 as char), 3, '0')
                        )) as concat_no
                from keiyaku_detail_table
                group by no1
              ) valid_key
      ) valid_key
      on    valid_key.no1 = dtl.no1
        and valid_key.no2 = dtl.no2
        and valid_key.no3 = dtl.no3
        and valid_key.no4 = dtl.no4
where area.area_cd1 = '10'
  and area.area_cd2 = '100'