我发现在MySQL中创建视图非常困难,希望有人能帮助我.这是我的数据库的模式:
CREATE DATABASE football;
CREATE TABLE `team` (
`name` varchar(15) PRIMARY KEY NOT NULL
);
CREATE TABLE `season` (
`name` char(9) PRIMARY KEY NOT NULL,
`begin` date,
`end` date);
CREATE TABLE `game` (
`id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
`Date` date DEFAULT NULL,
`HomeTeam` varchar(15) NOT NULL,
`AwayTeam` varchar(15) NOT NULL,
`FTHG` int DEFAULT NULL, (full time home goal)
`FTAG` int DEFAULT NULL, (full time away goal)
`FTR` char(1) DEFAULT NULL,
`HTHG` int DEFAULT NULL,
`HTAG` int DEFAULT NULL,
`HTR` char(1) DEFAULT NULL,
`H_S` int DEFAULT NULL,
`A_S` int DEFAULT NULL,
`HST` int DEFAULT NULL,
`AST` int DEFAULT NULL,
`HC` int DEFAULT NULL,
`AC` int DEFAULT NULL,
`HF` int DEFAULT NULL,
`AF` int DEFAULT NULL,
`HY` int DEFAULT NULL,
`AY` int DEFAULT NULL,
`HR` int DEFAULT NULL,
`AR` int DEFAULT NULL,
`season` char(9),
FOREIGN KEY (HomeTeam) REFERENCES team(name),
FOREIGN KEY (AwayTeam) REFERENCES team(name),
FOREIGN KEY (season) REFERENCES season(name),
CHECK (HomeTeam != AwayTeam)
);
这个数据库收集了一系列的足球比赛和每一场比赛的许多特征,以及所有的球队和赛季.
CREATE VIEW stats AS
(SELECT season,team,sum(TGS) TGS,sum(TGC) TGC,sum(pts) pts
FROM (SELECT season, HomeTeam team, FTHG TGS, FTAG TGC,
CASE WHEN FTHG > FTAG THEN 3 WHEN FTHG=FTAG THEN 1 ELSE 0 END pts
FROM game UNION ALL SELECT season, AwayTeam team, FTAG TGS, FTHG TGC,
CASE WHEN FTAG > FTHG THEN 3 WHEN FTAG=FTHG THEN 1 ELSE 0 END pts FROM game) games
GROUP BY season,team);
其结果如下:
SELECT * FROM stats;
View列依次为:赛季、eam、TGS(球队得分)、TGC(球队失球)
这很好,但我现在需要的是生成排名.理想情况下,我希望有如下内容:
Team,19,18,17,16,15,14 (years)
Juventus,1,1,1,1,1,1 (came first each year)
Inter,2,4,4,7,4,8
Atalanta,3,3,7,4,13,17
Lazio,4,8,5,5,8,3
Roma,5,6,3,2,3,2
Milan,6,5,6,6,7,10
Napoli,7,2,2,3,2,5
Sassuolo,8,11,11,10,6,12
Verona,9,,19,,,13
一个主要的困难是,正如你所看到的,排名是从第14年(2014-2015赛季)开始的.因此,数据库中不存在该数据.虽然这是一件坏事,但有可能在视图中对其进行硬编码吗?我总是需要每场比赛前一年的数据,所以添加旧游戏只会重现问题...
Note
我知道db struct 可能不是最好的,但我现在必须坚持下go ...