fetus Diary

こんなの作ってみた。いや、SQL って便利ね。この SQL、自分じゃ読めないものになっているけど…。

SQL は…

SELECT * FROM ( SELECT fielder_card_master.card_number, team_master.name AS team_name, position_master.name AS position_name, NULL AS pitch_part, CASE fielder_card_master.card_type WHEN 'G' THEN 'GR' WHEN 'S' THEN 'SP' WHEN 'B' THEN 'B' WHEN 'W' THEN 'W' ELSE NULL END AS card_type, fielder_card_master.name, fielder_card_master.power + fielder_card_master.meet + fielder_card_master.run + fielder_card_master.throw + fielder_card_master.fielding AS total, CASE WHEN having_card_data.num IS NULL THEN 0 ELSE having_card_data.num END AS num FROM fielder_card_master LEFT JOIN having_card_data ON fielder_card_master.card_number = having_card_data.card_number, team_master, position_master WHERE fielder_card_master.team_id = team_master.team_id AND fielder_card_master.position = position_master.position AND fielder_card_master.card_type = 'S' AND fielder_card_master.team_id = 3 AND having_card_data.num IS NOT NULL AND having_card_data.num > 0 UNION SELECT pitcher_card_master.card_number, team_master.name AS team_name, '投手' AS position_name, CASE pitcher_card_master.pitch_part WHEN 1 THEN '先発' WHEN 2 THEN '中継' WHEN 3 THEN '抑え' END AS pitch_part, CASE pitcher_card_master.card_type WHEN 'G' THEN 'GR' WHEN 'S' THEN 'SP' WHEN 'B' THEN 'B' WHEN 'W' THEN 'W' ELSE NULL END AS card_type, pitcher_card_master.name, pitcher_card_master.speed + pitcher_card_master.screwball + pitcher_card_master.control + pitcher_card_master.stamina + pitcher_card_master.fielding, CASE WHEN having_card_data.num IS NULL THEN 0 ELSE having_card_data.num END AS num FROM pitcher_card_master LEFT JOIN having_card_data ON pitcher_card_master.card_number = having_card_data.card_number, team_master WHERE pitcher_card_master.team_id = team_master.team_id AND pitcher_card_master.card_type = 'S' AND pitcher_card_master.team_id = 3 AND having_card_data.num IS NOT NULL AND having_card_data.num > 0 ) AS hoge ORDER BY get_sort_index(hoge.card_number)

こんな感じ。関数を order by が食べてくれるようにする方法が見つからなかったので、サブクエリ的に、野手と投手が別テーブルに居るので UNION まで使っていたり…。1 つの SQL 文中に SELECT が 3 つ居る、とても重いものになりますた orz