歐冠聯賽

SELECT unit.team_sn, ct.hk AS team, SUM(unit.win) AS win, SUM(unit.loss) AS loss, SUM(unit.draw) AS even, SUM(unit.pts) AS pts, SUM(unit.gap) AS gap, SUM(unit.hw) AS hw, SUM(unit.hl) AS hl, SUM(unit.he) AS he, SUM(unit.rw) AS rw, SUM(unit.rl) AS rl, SUM(unit.re) AS re, SUM(unit.hoffs) AS hoffs, SUM(unit.hdefs) AS hdefs, SUM(unit.aoffs) AS aoffs, SUM(unit.adefs) AS adefs, ct.en, ct.tw AS sltn, unit.grp, rsf.form FROM (SELECT rlf.rgs_sn, rlf.home, rlf.away, rlf.localtime, rlf.league, rlf.item, rlf.gameday, rlf.gametime, { fn UCASE(RIGHT(rlf.league, LEN(rlf.league) - 3)) } AS grp, rgk.hts, rgk.fts, rgk.tws, CASE WHEN rgk.team_sn = rlf.home AND rgk.tws = '主' OR rgk.team_sn = rlf.away AND rgk.tws = '客' THEN 1 ELSE 0 END AS win, CASE WHEN rgk.team_sn = rlf.home AND rgk.tws = '客' OR rgk.team_sn = rlf.away AND rgk.tws = '主' THEN 1 ELSE 0 END AS loss, CASE WHEN rgk.tws = '和' THEN 1 ELSE 0 END AS draw, CASE WHEN rgk.team_sn = rlf.home AND rgk.tws = '主' THEN 1 ELSE 0 END AS hw, CASE WHEN rgk.team_sn = rlf.home AND rgk.tws = '客' THEN 1 ELSE 0 END AS hl, CASE WHEN rgk.team_sn = rlf.home AND rgk.tws = '和' THEN 1 ELSE 0 END AS he, CASE WHEN rgk.team_sn = rlf.away AND rgk.tws = '主' THEN 1 ELSE 0 END AS rw, CASE WHEN rgk.team_sn = rlf.away AND rgk.tws = '客' THEN 1 ELSE 0 END AS rl, CASE WHEN rgk.team_sn = rlf.home AND rgk.tws = '主' OR rgk.team_sn = rlf.away AND rgk.tws = '客' THEN 3 WHEN rgk.tws = '和' THEN 1 ELSE 0 END AS pts, CASE WHEN rgk.team_sn = rlf.away AND rgk.tws = '和' THEN 1 ELSE 0 END AS re, rgk.team_sn, agk.hts AS ahs, agk.fts AS afs, rgk.fts - agk.fts AS gap, CASE WHEN rgk.team_sn = rlf.home THEN rgk.fts END AS hoffs, CASE WHEN rgk.team_sn = rlf.home THEN agk.fts END AS hdefs, CASE WHEN rgk.team_sn = rlf.away THEN rgk.fts END AS aoffs, CASE WHEN rgk.team_sn = rlf.away THEN agk.fts END AS adefs FROM C277168_soccer.dbo.record_game_kpi AS agk RIGHT OUTER JOIN C277168_soccer.dbo.record_game_kpi AS rgk ON agk.team_sn <> rgk.team_sn AND agk.rgs_sn = rgk.rgs_sn RIGHT OUTER JOIN C277168_soccer.dbo.record_league_fixtures AS rlf ON rgk.rgs_sn = rlf.rgs_sn WHERE (LEFT(rlf.league, 3) = 'ucl') AND (rlf.item = 2015) AND (NOT (RIGHT(rlf.league, LEN(rlf.league) - 3) IN ('1', '4', '8', '16', '32')))) AS unit LEFT OUTER JOIN C277168_soccer.dbo.record_season_form AS rsf ON unit.item = rsf.item AND unit.team_sn = rsf.team_sn LEFT OUTER JOIN C277168_soccer.dbo.cfg_team AS ct ON unit.team_sn = ct.team_sn WHERE (rsf.item = 2015) GROUP BY unit.team_sn, unit.grp, ct.tw, ct.cn, ct.hk, ct.en, rsf.form ORDER BY unit.grp, pts DESC