裏技83-90のSQL

最終更新時間:2011年04月23日 17時14分11秒
---- 裏技83
SELECT
  '83' 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 = '04' -- 新潟
AND (RA.TRACK_CODE = '11' OR RA.TRACK_CODE = '10' OR RA.TRACK_CODE = '12') -- 芝左 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
;

---- 裏技84
SELECT
  '' AS URAWAZA_TANSHO
,  '84' 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 = '04' -- 新潟
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
;

---- 裏技85
SELECT
  '85' 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 = '04' -- 新潟
AND (RA.TRACK_CODE = '10' OR RA.TRACK_CODE = '11' OR RA.TRACK_CODE = '12') -- 芝左 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
;

---- 裏技86
SELECT
  '' AS URAWAZA_TANSHO
,  '86' 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 = '04' -- 新潟
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
;

---- 裏技87
SELECT
  '87' 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 = '04' -- 新潟
AND (RA.TRACK_CODE = '11' OR RA.TRACK_CODE = '10' OR RA.TRACK_CODE = '12') -- 芝左 or 芝直線
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
;
---- 裏技88
SELECT
  '' AS URAWAZA_TANSHO
,  '88' 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
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 = '04' -- 新潟
AND (RA.TRACK_CODE = '11' OR RA.TRACK_CODE = '10' OR RA.TRACK_CODE = '12') -- 芝左 or 芝直線
AND RA.KYORI = 1600 -- 距離
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
;

---- 裏技89
SELECT
  '89' 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 = '04' -- 新潟
AND (RA.TRACK_CODE = '11' OR RA.TRACK_CODE = '10' OR RA.TRACK_CODE = '12') -- 芝左 or 芝直線
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
;

---- 裏技90
SELECT
  '90' 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 = '04' -- 新潟
AND RA.KYOSO_JOKEN_CODE_SAIJAKUNEN = '703' -- 未勝利
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
;

関連ページ