体育近代史
体育近代史
提问于 2016-10-01 21:16:24
SQL示例如下：
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查询来实现，如果可能的话，对于来自table的每一支球队来说，将前两个结果显示为W、L或D(赢输或平局)以及他们与谁比赛。我知道，如果“固定状态”被设置为“完成”，那么游戏就完成了，为了建立最近的两个，我可以在下降的顺序中使用“固定日期”字段。
输出可以类似于：
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 ;任何帮助都会很好，非常感谢，艾伦。
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-10-03 00:49:31
并不是所有事情都可以通过sql简单地完成，有时某些事情应该用编程语言来完成，但是只要问题没有指定编程语言，我就会提供一些有用的视图和查询。
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
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39811545
复制相关文章
相似问题
腾讯云开发者
