英超聯賽

SELECT sct.team_sn, alltime.team, alltime.win, alltime.loss, alltime.even, alltime.pts, alltime.gap, hometeam.win AS hw, hometeam.loss AS hl, hometeam.even AS he, roadteam.win AS rw, roadteam.loss AS rl, roadteam.even AS re, ISNULL(hometeam.hoffs, 0) AS hoffs, ISNULL(hometeam.hdefs, 0) AS hdefs, ISNULL(roadteam.aoffs, 0) AS aoffs, ISNULL(roadteam.adefs, 0) AS adefs, sct.en, sct.tw AS sltn, cast(rsf.form as varchar(8000)) as form FROM C277168_soccer.dbo.record_season_form AS rsf RIGHT OUTER JOIN (SELECT team_sn, sina, en, tw FROM C277168_soccer.dbo.cfg_team) AS sct ON rsf.team_sn = sct.team_sn RIGHT OUTER JOIN (SELECT TOP (1000) team_sn, CASE WHEN rgs = 'A' THEN atn ELSE htn END AS team, SUM(CASE WHEN su = '勝' THEN 1 ELSE 0 END) AS win, SUM(CASE WHEN su = '負' THEN 1 ELSE 0 END) AS loss, SUM(CASE WHEN su = '走' THEN 1 ELSE 0 END) AS even, SUM(CASE WHEN su = '勝' THEN 1 ELSE 0 END) * 3 + SUM(CASE WHEN su = '走' THEN 1 ELSE 0 END) AS pts, SUM(offs) - SUM(defs) AS gap FROM C277168_pfb.dbo.record_overall_game_stats WHERE (league = 'epl') AND (item = 2017) GROUP BY team_sn, CASE WHEN rgs = 'A' THEN atn ELSE htn END ORDER BY pts DESC, gap DESC, win DESC, loss) AS alltime ON sct.sina = alltime.team_sn LEFT OUTER JOIN (SELECT TOP (1000) team_sn, CASE WHEN rgs = 'A' THEN atn ELSE htn END AS team, SUM(CASE WHEN su = '勝' THEN 1 ELSE 0 END) AS win, SUM(CASE WHEN su = '負' THEN 1 ELSE 0 END) AS loss, SUM(CASE WHEN su = '走' THEN 1 ELSE 0 END) AS even, SUM(CASE WHEN su = '勝' THEN 1 ELSE 0 END) * 3 + SUM(CASE WHEN su = '走' THEN 1 ELSE 0 END) AS pts, SUM(offs) - SUM(defs) AS gap, SUM(offs) AS aoffs, SUM(defs) AS adefs FROM C277168_pfb.dbo.record_overall_game_stats AS record_overall_game_stats_1 WHERE (league = 'epl') AND (item = 2017) AND (rgs = 'A') GROUP BY team_sn, CASE WHEN rgs = 'A' THEN atn ELSE htn END ORDER BY pts DESC, gap DESC, win DESC, loss) AS roadteam ON alltime.team_sn = roadteam.team_sn LEFT OUTER JOIN (SELECT TOP (1000) team_sn, CASE WHEN rgs = 'A' THEN atn ELSE htn END AS team, SUM(CASE WHEN su = '勝' THEN 1 ELSE 0 END) AS win, SUM(CASE WHEN su = '負' THEN 1 ELSE 0 END) AS loss, SUM(CASE WHEN su = '走' THEN 1 ELSE 0 END) AS even, SUM(CASE WHEN su = '勝' THEN 1 ELSE 0 END) * 3 + SUM(CASE WHEN su = '走' THEN 1 ELSE 0 END) AS pts, SUM(offs) - SUM(defs) AS gap, SUM(offs) AS hoffs, SUM(defs) AS hdefs FROM C277168_pfb.dbo.record_overall_game_stats AS record_overall_game_stats_1 WHERE (league = 'epl') AND (item = 2017) AND (rgs = 'H') GROUP BY team_sn, CASE WHEN rgs = 'A' THEN atn ELSE htn END) AS hometeam ON alltime.team_sn = hometeam.team_sn WHERE (rsf.item = 2017) ORDER BY alltime.pts DESC, alltime.gap DESC, alltime.win DESC, alltime.loss