裏技18-47のSQL
最終更新時間:2011年01月14日 21時19分57秒
---- 裏技18 -- 前走の情報を保持するテーブルを別途作成 DROP TABLE IF EXISTS URWZ18A; CREATE TEMPORARY TABLE IF NOT EXISTS URWZ18A AS SELECT UM.KETTO_TOROKU_BANGO AS ZENSO_KETTO_TOROKU_BANGO , MAX(RA.RACE_CODE) AS ZENSO_RACE_CODE FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN = '7' AND UM.KETTO_TOROKU_BANGO IN ( SELECT UN.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UN INNER JOIN JVD_RACE_SHOSAI RB ON UN.RACE_CODE = RB.RACE_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UN.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RB.DATA_KUBUN <= '6' AND UN.UMABAN <> '00' -- 馬番 -- AND RB.KEIBAJO_CODE = '06' -- 中山 AND (RB.TRACK_CODE = '17' OR RB.TRACK_CODE = '18') -- 芝内 or 芝外 AND RB.KYORI = 2000 -- 距離 ) GROUP BY UM.KETTO_TOROKU_BANGO ORDER BY RA.RACE_CODE ASC ; DROP TABLE IF EXISTS URWZ18B; CREATE TEMPORARY TABLE IF NOT EXISTS URWZ18B AS SELECT ZENSO_KETTO_TOROKU_BANGO , ZENSO_RACE_CODE , RA.KEIBAJO_CODE AS ZENSO_KEIBAJO_CODE , RA.TRACK_CODE AS ZENSO_TRACK_CODE , RA.KAISAI_NENGAPPI AS ZENSO_NENGAPPI FROM URWZ18A UR INNER JOIN JVD_RACE_SHOSAI RA ON UR.ZENSO_RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE ; SELECT '' AS URAWAZA_TANSHO , '18' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO --, DATE(UR.ZENSO_NENGAPPI) AS ZENSO_NENGAPPI --,(JULIANDAY(DATE(RA.KAISAI_NENGAPPI)) - JULIANDAY(UR.ZENSO_NENGAPPI)) AS KANKAKU FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE INNER JOIN URWZ18B UR ON UM.KETTO_TOROKU_BANGO = UR.ZENSO_KETTO_TOROKU_BANGO LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝内 or 芝外 AND RA.KYORI = 2000 -- 距離 AND UR.ZENSO_KEIBAJO_CODE = '06' -- 前走も中山 AND (JULIANDAY(RA.KAISAI_NENGAPPI) - JULIANDAY(UR.ZENSO_NENGAPPI)) >= 63 -- 前走から中9週以上 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技19 SELECT '' AS URAWAZA_TANSHO , '19*' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝内 or 芝外 AND (RA.KYOSO_SHUBETSU_CODE <> '11' AND RA.KYOSO_SHUBETSU_CODE <> '12') -- 2歳限定でなく、かつ、3歳限定でない AND PE.KETTO1_BAMEI = 'オペラハウス' -- 父 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技20 SELECT '' AS URAWAZA_TANSHO , '20*' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝内 or 芝外 AND RA.KYORI <= 2000 -- 距離 AND RA.GRADE_CODE = ' ' -- 平場 AND PE.KETTO1_BAMEI = 'ブライアンズタイム' -- 父 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技21 SELECT '21*' AS URAWAZA_TANSHO , '' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝内 or 芝外 AND (RA.KYOSO_SHUBETSU_CODE = '11' OR RA.KYOSO_SHUBETSU_CODE = '12') -- 2歳限定 or 3歳限定 AND UM.CHOKYOSHIMEI_RYAKUSHO = '田村康仁' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技22 SELECT '' AS URAWAZA_TANSHO , '22' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND RA.TRACK_CODE = '24' -- ダート AND PE.KETTO1_BAMEI = 'スウェプトオーヴァーボード' -- 父 AND UM.SEIBETSU_CODE = '1' -- 牡 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技23 SELECT '' AS URAWAZA_TANSHO , '23' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND RA.TRACK_CODE = '24' -- ダート AND PE.KETTO1_BAMEI = 'チーフベアハート' -- 父 AND UM.SEIBETSU_CODE = '1' -- 牡 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技24 SELECT '' AS URAWAZA_TANSHO , '24*' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND RA.TRACK_CODE = '24' -- ダート AND RA.KYOSO_SHUBETSU_CODE = '11' -- 2歳限定 AND UM.CHOKYOSHIMEI_RYAKUSHO = '尾形充弘' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技25 SELECT '25' AS URAWAZA_TANSHO , '' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND RA.TRACK_CODE = '24' -- ダート AND UM.CHOKYOSHIMEI_RYAKUSHO = '小島茂之' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技26 SELECT '' AS URAWAZA_TANSHO , '26*' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND RA.TRACK_CODE = '24' -- ダート AND RA.KYORI = 1200 -- 距離 AND (RA.GRADE_CODE = 'A' OR RA.GRADE_CODE = 'B' OR RA.GRADE_CODE = 'C' OR RA.GRADE_CODE = 'D' OR RA.GRADE_CODE = 'E') -- 重賞 or 特別 AND UM.CHOKYOSHIMEI_RYAKUSHO = '森秀行' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技27 SELECT '' AS URAWAZA_TANSHO , '27' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝内 or 芝外 AND RA.KYORI <= 2000 -- 距離 AND UM.KISHUMEI_RYAKUSHO = '藤田伸二' AND UM.WAKUBAN <= 3 -- 枠番 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技28 SELECT '' AS URAWAZA_TANSHO , '28' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '06' -- 中山 AND RA.TRACK_CODE = '24' -- ダート AND UM.KISHUMEI_RYAKUSHO = '木幡初広' AND (UM.WAKUBAN >= 2 AND UM.WAKUBAN <= 7)-- 枠番 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技29 SELECT '29' AS URAWAZA_TANSHO , '' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝右内 or 芝右外 AND SUBSTR(DATE(RA.KAISAI_NENGAPPI),6,1) = '0' AND SUBSTR(DATE(RA.KAISAI_NENGAPPI),7,1) >= '1' AND SUBSTR(DATE(RA.KAISAI_NENGAPPI),7,1) <= '6' -- 1月から6月 AND PE.KETTO1_BAMEI = 'ホワイトマズル' -- 父 AND (UM.SEIBETSU_CODE = '1' OR UM.SEIBETSU_CODE = '3') -- 牡 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技30 SELECT '30' AS URAWAZA_TANSHO , '' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝右内 or 芝右外 AND RA.KYOSO_SHUBETSU_CODE = '11' -- 2歳限定 AND PE.KETTO5_BAMEI = 'サンデーサイレンス' -- 母父 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技31 -- 前走の情報を保持するテーブルを別途作成 DROP TABLE IF EXISTS URWZ31A; CREATE TEMPORARY TABLE IF NOT EXISTS URWZ31A AS SELECT UM.KETTO_TOROKU_BANGO AS ZENSO_KETTO_TOROKU_BANGO , MAX(RA.RACE_CODE) AS ZENSO_RACE_CODE FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN = '7' AND UM.KETTO_TOROKU_BANGO IN ( SELECT UN.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UN INNER JOIN JVD_RACE_SHOSAI RB ON UN.RACE_CODE = RB.RACE_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UN.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RB.DATA_KUBUN <= '6' AND UN.UMABAN <> '00' -- 馬番 -- AND RB.KEIBAJO_CODE = '08' -- 京都 AND RB.TRACK_CODE = '18' -- 芝内 or 芝外 AND RB.KYORI = 1400 -- 距離 ) GROUP BY UM.KETTO_TOROKU_BANGO ORDER BY RA.RACE_CODE ASC ; DROP TABLE IF EXISTS URWZ31B; CREATE TEMPORARY TABLE IF NOT EXISTS URWZ31B AS SELECT ZENSO_KETTO_TOROKU_BANGO , ZENSO_RACE_CODE , UM.CORNER4_JUNI AS ZENSO_CORNER4_JUNI , RA.TRACK_CODE AS ZENSO_TRACK_CODE , RA.KAISAI_NENGAPPI AS ZENSO_NENGAPPI FROM URWZ31A UR INNER JOIN JVD_UMAGOTO_RACE_JOHO UM ON UR.ZENSO_RACE_CODE = UM.RACE_CODE AND UR.ZENSO_KETTO_TOROKU_BANGO = UM.KETTO_TOROKU_BANGO INNER JOIN JVD_RACE_SHOSAI RA ON UR.ZENSO_RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE ; SELECT '' AS URAWAZA_TANSHO , '31*' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO --, US.ZENSO_CORNER4_JUNI AS ZENSO_CORNER4_JUNI --, DATE(US.ZENSO_NENGAPPI) AS ZENSO_NENGAPPI --,(JULIANDAY(DATE(RA.KAISAI_NENGAPPI)) - JULIANDAY(UR.ZENSO_NENGAPPI)) AS KANKAKU FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE INNER JOIN URWZ31B US ON UM.KETTO_TOROKU_BANGO = US.ZENSO_KETTO_TOROKU_BANGO LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '18' -- 芝外 AND RA.KYORI = 1400 -- 距離 AND US.ZENSO_CORNER4_JUNI =1 -- 前走4コーナーを1番手で通過 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技32 SELECT '' AS URAWAZA_TANSHO , '32' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝右内 or 芝右外 AND UM.CHOKYOSHIMEI_RYAKUSHO = '長浜博之' -- 調教師名 AND UM.BAREI <= 3 -- 3歳以下 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技33 SELECT '' AS URAWAZA_TANSHO , '33' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝右内 or 芝右外 AND UM.CHOKYOSHIMEI_RYAKUSHO = '加用正' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技34 SELECT '' AS URAWAZA_TANSHO , '34' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝右内 or 芝右外 AND UM.CHOKYOSHIMEI_RYAKUSHO = '笹田和秀' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技35 SELECT '' AS URAWAZA_TANSHO , '35' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND (RA.TRACK_CODE = '17' OR RA.TRACK_CODE = '18') -- 芝右内 or 芝右外 AND RA.KYORI >= 1800 -- 距離 AND (RA.GRADE_CODE = 'A' OR RA.GRADE_CODE = 'B' OR RA.GRADE_CODE = 'C' OR RA.GRADE_CODE = 'D') -- 重賞 AND UM.TOZAI_SHOZOKU_CODE = '1' -- 関東の厩舎に所属 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技36 SELECT '' AS URAWAZA_TANSHO , '36' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '18' -- 芝右外 AND UM.CHOKYOSHIMEI_RYAKUSHO = '音無秀孝' -- 調教師名 AND UM.BAREI >= 4 -- 4歳以上 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技37 -- 前走の情報を保持するテーブルを別途作成 DROP TABLE IF EXISTS URWZ37A; CREATE TEMPORARY TABLE IF NOT EXISTS URWZ37A AS SELECT UM.KETTO_TOROKU_BANGO AS ZENSO_KETTO_TOROKU_BANGO , MAX(RA.RACE_CODE) AS ZENSO_RACE_CODE FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN = '7' AND UM.KETTO_TOROKU_BANGO IN ( SELECT UN.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UN INNER JOIN JVD_RACE_SHOSAI RB ON UN.RACE_CODE = RB.RACE_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UN.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RB.DATA_KUBUN <= '6' AND UN.UMABAN <> '00' -- 馬番 -- AND RB.KEIBAJO_CODE = '08' -- 京都 AND RB.TRACK_CODE = '24' -- ダート右回り AND RB.KYORI = 1900 -- 距離 ) GROUP BY UM.KETTO_TOROKU_BANGO ORDER BY RA.RACE_CODE ASC ; DROP TABLE IF EXISTS URWZ37B; CREATE TEMPORARY TABLE IF NOT EXISTS URWZ37B AS SELECT ZENSO_KETTO_TOROKU_BANGO , ZENSO_RACE_CODE , RA.TRACK_CODE AS ZENSO_TRACK_CODE , RA.KAISAI_NENGAPPI AS ZENSO_NENGAPPI FROM URWZ37A UR INNER JOIN JVD_UMAGOTO_RACE_JOHO UM ON UR.ZENSO_KETTO_TOROKU_BANGO = UM.KETTO_TOROKU_BANGO INNER JOIN JVD_RACE_SHOSAI RA ON UR.ZENSO_RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO ; SELECT '' AS URAWAZA_TANSHO , '37' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO --, DATE(UR.ZENSO_NENGAPPI) AS ZENSO_NENGAPPI --,(JULIANDAY(DATE(RA.KAISAI_NENGAPPI)) - JULIANDAY(UR.ZENSO_NENGAPPI)) AS KANKAKU FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE INNER JOIN URWZ37B UR ON UM.KETTO_TOROKU_BANGO = UR.ZENSO_KETTO_TOROKU_BANGO LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート右回り AND RA.KYORI = 1900 -- 距離 AND UR.ZENSO_TRACK_CODE = '24' -- 前走もダート右回り AND (JULIANDAY(RA.KAISAI_NENGAPPI) - JULIANDAY(UR.ZENSO_NENGAPPI)) >= 63 -- 前走から中9週以上 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技38 SELECT '' AS URAWAZA_TANSHO , '38' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND PE.KETTO5_BAMEI = 'ブライアンズタイム' -- 母父 AND UM.SEIBETSU_CODE = '1' -- 牡 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技39 SELECT '' AS URAWAZA_TANSHO , '39*' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND PE.KETTO1_BAMEI = 'ゼンノロブロイ' -- 父 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技40 SELECT '' AS URAWAZA_TANSHO , '40' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND RA.KYORI = 1200 -- 距離 AND PE.KETTO1_BAMEI = 'クロフネ' -- 父 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技41 SELECT '' AS URAWAZA_TANSHO , '41' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND RA.GRADE_CODE = ' ' -- 平場 AND UM.CHOKYOSHIMEI_RYAKUSHO = '岡田稲男' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技42 SELECT '' AS URAWAZA_TANSHO , '42' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND (RA.KYOSO_SHUBETSU_CODE <> '11' AND RA.KYOSO_SHUBETSU_CODE <> '12') -- 2歳限定でなく、かつ、3歳限定でない-- AND UM.CHOKYOSHIMEI_RYAKUSHO = '鮫島一歩' -- 調教師名 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技43 SELECT '' AS URAWAZA_TANSHO , '43' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND RA.KYORI = 1800 -- 距離 AND RA.GRADE_CODE = ' ' -- 平場 AND ( -- 500万下以下 (RA.KYOSO_JOKEN_CODE_2SAI <> '000' AND (RA.KYOSO_JOKEN_CODE_2SAI <= '005' OR RA.KYOSO_JOKEN_CODE_2SAI >= '701')) OR (RA.KYOSO_JOKEN_CODE_3SAI <> '000' AND (RA.KYOSO_JOKEN_CODE_3SAI <= '005' OR RA.KYOSO_JOKEN_CODE_3SAI >= '701')) OR (RA.KYOSO_JOKEN_CODE_4SAI <> '000' AND RA.KYOSO_JOKEN_CODE_4SAI <= '005') OR (RA.KYOSO_JOKEN_CODE_5SAI_IJO <> '000' AND RA.KYOSO_JOKEN_CODE_5SAI_IJO <= '005') ) AND UM.UMABAN = ( -- 大外枠 SELECT MAX(UM.UMABAN) FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RB ON UM.RACE_CODE = RB.RACE_CODE WHERE 1=1 AND RB.RACE_CODE = RA.RACE_CODE ) -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技44 SELECT '44' AS URAWAZA_TANSHO , '' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '18' -- 芝右外 AND UM.KISHUMEI_RYAKUSHO = '池添謙一' AND UM.BAREI <= 5 -- 5歳以下 -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技45 SELECT '' AS URAWAZA_TANSHO , '45' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '17' -- 芝右内 AND (RA.GRADE_CODE = 'A' OR RA.GRADE_CODE = 'B' OR RA.GRADE_CODE = 'C' OR RA.GRADE_CODE = 'D' OR RA.GRADE_CODE = 'E') -- 重賞 or 特別 AND UM.KISHUMEI_RYAKUSHO = '藤田伸二' -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技46 SELECT '' AS URAWAZA_TANSHO , '46' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND RA.KYOSO_SHUBETSU_CODE = '11' -- 2歳限定 AND UM.KISHUMEI_RYAKUSHO = '藤岡佑介' -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ; ---- 裏技47 SELECT '47' AS URAWAZA_TANSHO , '' AS URAWAZA_FUKUSHO , DATE(RA.KAISAI_NENGAPPI) AS NENGAPPI , KE.CONTENT AS KEIBAJO , RA.RACE_BANGO AS RACE_BANGO , KS.CONTENT AS KYOSO_SHUBETSU , KJ.CONTENT AS KYOSO_JOKEN , RA.KYOSOMEI_HONDAI AS KYOSO_HONDAI , RA.KYORI AS KYORI , TR.CONTENT AS TRACK , UM.WAKUBAN AS WAKUBAN , UM.UMABAN AS UMABAN , UM.BAMEI AS BAMEI , SE.CONTENT AS SEIBETSU , UM.BAREI AS BAREI , UM.KISHUMEI_RYAKUSHO AS KISHU , UM.FUTAN_JURYO AS KINRYO , UM.CHOKYOSHIMEI_RYAKUSHO AS CHOKYOSHI , PE.KETTO1_BAMEI AS TITI , PE.KETTO5_BAMEI AS HAHATITI , RA.RACE_CODE , UM.KETTO_TOROKU_BANGO FROM JVD_UMAGOTO_RACE_JOHO UM INNER JOIN JVD_RACE_SHOSAI RA ON UM.RACE_CODE = RA.RACE_CODE LEFT OUTER JOIN JVD_KEIBAJO_CODE KE ON KE.CODE = RA.KEIBAJO_CODE LEFT OUTER JOIN JVD_KYOSO_SHUBETSU_CODE KS ON KS.CODE = RA.KYOSO_SHUBETSU_CODE LEFT OUTER JOIN JVD_KYOSO_JOKEN_CODE KJ ON KJ.CODE = RA.KYOSO_JOKEN_CODE_SAIJAKUNEN LEFT OUTER JOIN JVD_TRACK_CODE TR ON TR.CODE = RA.TRACK_CODE LEFT OUTER JOIN JVD_SEIBETSU_CODE SE ON SE.CODE = UM.SEIBETSU_CODE LEFT OUTER JOIN JVD_KYOSOBA_MASTER PE ON UM.KETTO_TOROKU_BANGO = PE.KETTO_TOROKU_BANGO WHERE 1 = 1 AND RA.DATA_KUBUN <= '6' AND UM.UMABAN <> '00' -- 馬番 -- AND RA.KEIBAJO_CODE = '08' -- 京都 AND RA.TRACK_CODE = '24' -- ダート AND RA.KYOSO_SHUBETSU_CODE <> '11' -- 2歳限定以外 AND UM.KISHUMEI_RYAKUSHO = '池添謙一' -- GROUP BY RA.RACE_CODE , RA.KAISAI_NENGAPPI , KE.CONTENT , KS.CONTENT , KJ.CONTENT , RA.KYORI , TR.CONTENT , UM.WAKUBAN , UM.UMABAN ORDER BY RA.RACE_CODE ASC ;
関連ページ