裏技68-75のSQL

最終更新時間:2011年06月19日 17時15分45秒
†---- 裏技68
SELECT
  '68' 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 = '02' -- 函館
AND RA.TRACK_CODE = '17' -- 芝右
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
;

---- 裏技69
SELECT
  '69' 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 = '02' -- 函館
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
;

---- 裏技70
SELECT
  '70' 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 = '02' -- 函館
AND PE.KETTO5_BAMEI = 'Woodman' -- 母父
--
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
;

---- 裏技71
SELECT
  '71' 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 = '02' -- 函館
AND RA.TRACK_CODE = '17' -- 芝右
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
;

---- 裏技72
SELECT
  '72' 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 = '02' -- 函館
AND RA.TRACK_CODE = '17' -- 芝右
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
;
---- 裏技73
SELECT
  '73' 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
, UM.BANUSHIMEI_HOJINKAKU_NASHI AS BANUSHI
, 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
LEFT OUTER JOIN JVD_KISHU_MASTER KM
ON KM.KISHU_CODE = UM.KISHU_CODE
WHERE 1 = 1
AND RA.DATA_KUBUN <= '6'
AND UM.UMABAN <> '00' -- 馬番
--
AND RA.KEIBAJO_CODE = '02' -- 函館
AND UM.BANUSHIMEI_HOJINKAKU_NASHI = '松本 好雄'
--
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
;

---- 裏技74
SELECT
  '74' 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 = '02' -- 函館
AND RA.TRACK_CODE = '17' -- 芝右
AND LIKE("メジロ%",UM.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
;

---- 裏技75
SELECT
  '' AS URAWAZA_TANSHO
,  '75' 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 = '02' -- 函館
AND RA.KYOSO_JOKEN_CODE_SAIJAKUNEN = '703' -- 未勝利
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
;