mysql - 体育近史

Question

下面的 SQL Fiddle 示例:

http://sqlfiddle.com/#!9/75561/1

我在 MySQL 中有下表:

CREATE TABLE `straightred_fixture` (
  `fixtureid` int(11) NOT NULL,
  `fixturedate` datetime(6) DEFAULT NULL,
  `fixturestatus` varchar(24) DEFAULT NULL,
  `fixturematchday` int(11) NOT NULL,
  `spectators` int(11) DEFAULT NULL,
  `hometeamscore` int(11) DEFAULT NULL,
  `awayteamscore` int(11) DEFAULT NULL,
  `homegoaldetails` longtext,
  `awaygoaldetails` longtext,
  `hometeamyellowcarddetails` longtext,
  `awayteamyellowcarddetails` longtext,
  `hometeamredcarddetails` longtext,
  `awayteamredcarddetails` longtext,
  `awayteamid` int(11) NOT NULL,
  `hometeamid` int(11) NOT NULL,
  `soccerseasonid` int(11) NOT NULL,
  PRIMARY KEY (`fixtureid`),
  KEY `straightred_fixture_2e879a39` (`awayteamid`),
  KEY `straightred_fixture_bcb6decb` (`hometeamid`),
  KEY `straightred_fixture_d6d641f1` (`soccerseasonid`),
  KEY `straightred_fixture_fixturematchday2_f98c3a75_uniq` (`fixturematchday`),
  CONSTRAINT `D9b896edf0aff4d9b5c00682a8e21ea3` FOREIGN KEY (`fixturematchday`) REFERENCES `straightred_fixturematchday` (`fixturematchdayid`),
  CONSTRAINT `straightr_soccerseasonid_92496b92_fk_straightred_season_seasonid` FOREIGN KEY (`soccerseasonid`) REFERENCES `straightred_season` (`seasonid`),
  CONSTRAINT `straightred_fixtu_awayteamid_3d1961ba_fk_straightred_team_teamid` FOREIGN KEY (`awayteamid`) REFERENCES `straightred_team` (`teamid`),
  CONSTRAINT `straightred_fixtu_hometeamid_6e37e94b_fk_straightred_team_teamid` FOREIGN KEY (`hometeamid`) REFERENCES `straightred_team` (`teamid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

如果可能的话，我希望使用 SQL 查询实现的是，对于球队表中的每个球队，将前 2 个结果显示为 W、L 或 D(赢、输或平)以及他们的对手。如果“fixturestatus”设置为“Finished”，我知道游戏已完成，并且为了建立最近的两个游戏，我可以按降序使用“fixturedate”字段。

输出可能类似于:

team id    Game 1 Result  Game 1 Opponent     Game 2      Game 2 Opponent
  12         W                 15                D             45
  13         L                 45                L             36

下面是团队表的副本，它可能有助于回答:

CREATE TABLE `straightred_team` (
  `teamid` int(11) NOT NULL,
  `teamname` varchar(36) NOT NULL,
  `country` varchar(36) DEFAULT NULL,
  `stadium` varchar(36) DEFAULT NULL,
  `homepageurl` longtext,
  `wikilink` longtext,
  `teamcode` varchar(5) DEFAULT NULL,
  `teamshortname` varchar(24) DEFAULT NULL,
  `currentteam` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`teamid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

任何帮助都会很棒，非常感谢，艾伦。

Answer 1

并不是所有事情都可以简单地通过 sql 完成，有时有些事情应该用编程语言完成，但只要问题没有指定编程语言，我就会提供一些有用的 View 和查询

CREATE VIEW united_result AS 
SELECT fixtureid, fixturedate, fixturestatus, hometeamid as team,
awayteamid as opponent, 
(CASE WHEN (hometeamscore-awayteamscore)>0 THEN 'W' 
WHEN (hometeamscore-awayteamscore)<0 THEN 'L' ELSE 'D' END) as result, 
'home' as mstatus
FROM straightred_fixture
UNION
SELECT fixtureid, fixturedate, fixturestatus, awayteamid as team, 
hometeamid as opponent, 
(CASE WHEN (hometeamscore-awayteamscore)<0 THEN 'W' 
WHEN (hometeamscore-awayteamscore)>0 THEN 'L' ELSE 'D' END) as result, 
'away' as mstatus
FROM straightred_fixture;

create or replace view plain_result as select fixtureid, fixturedate, 
team, opponent, result, mstatus 
from united_result where fixturestatus='Finished' order by team asc, 
fixturedate desc;

select 
team, 
group_concat( 
concat(result, ' against ', opponent, ' on ', date_format(fixturedate, '%D %M %Y')) 
order by fixturedate desc separator ' | ') as output 
from plain_result 
where (select count(*)
   from plain_result as p
   where plain_result.team = p.team 
   and p.fixturedate>=plain_result.fixturedate) <= 2
group by team;

你可以在这里尝试一下 http://sqlfiddle.com/#!9/5ce8f1/11