获得每支球队每年的足球犯规次数

vof42yt1 于 2022-10-03 发布在其他

关注(0)|答案(2)|浏览(137)

我正在努力统计每支球队每年的红牌犯规次数。

下面是我的样例数据，后面是DDL+DML。

足球队
Team_id|团队名称

1|A队
2|B队
3|Team C

匹配

Match_id|Match_Date

1|2018年01月01日
2|2018-02-03-2018
3|2018-05-04
4|2018年01月01日
5|2019-02-02
6|2019年07月04日
7|2019-06-13
8|2019-08-18
9|03-01-2020
10|06-02-2020
11|09-03-2020
12|12-04-2020

犯规

Ful_id|Team_id|Match_id|ful_type
-|-|
1|1|1|Y
2|3|5|Y
3|3|10|Y
4|3|10|R
5|2|11|Y
6|2|11|R
7|3|11|R
8|3|11|R

DDL+DML：

CREATE TABLE dbo.soccer_team (
    team_id   int         NOT NULL IDENTITY,
    team_name varchar(50) NOT NULL,

    CONSTRAINT PK_soccer_team PRIMARY KEY ( team_id )
);

CREATE TABLE dbo."match" (
    match_id   int  NOT NULL IDENTITY,
    match_date date NOT NULL,

    CONSTRAINT PK_match PRIMARY KEY ( match_id )
);

CREATE TABLE dbo.foul (
    foul_id   int     NOT NULL IDENTITY,
    team_id   int     NOT NULL,
    match_id  int     NOT NULL,
    foul_type char(1) NOT NULL,

    CONSTRAINT PK_foul PRIMARY KEY ( foul_id ),

    CONSTRAINT FK_foul_match FOREIGN KEY(match_id) REFERENCES dbo.match (match_id),
    CONSTRAINT FK_foul_soccer_team FOREIGN KEY(team_id) REFERENCES dbo.soccer_team (team_id)
);

----

SET IDENTITY_INSERT dbo.soccer_team ON;
SET IDENTITY_INSERT dbo."match" ON;
SET IDENTITY_INSERT dbo.foul ON;

INSERT INTO dbo.soccer_team ( team_id, team_name )
VALUES
(1, 'Team A'),
(2, 'Team B'),
(3, 'Team C');

INSERT INTO dbo.match ( match_id, match_date )
VALUES
(1, '01-01-2018'),
(2, '02-03-2018'),
(3, '05-04-2018'),
(4, '01-01-2018'),
(5, '02-02-2019'),
(6, '07-04-2019'),
(7, '06-13-2019'),
(8, '08-18-2019'),
(9, '03-01-2020'),
(10, '06-02-2020'),
(11, '09-03-2020'),
(12, '12-04-2020');

INSERT INTO dbo.foul (foul_id, team_id, match_id, foul_type )
VALUES
(1, 1, 1, 'Y'),
(2, 3, 5, 'Y'),
(3, 3, 10, 'Y'),
(4, 3, 10, 'R'),
(5, 2, 11, 'Y'),
(6, 2, 11, 'R'),
(7, 3, 11, 'R'),
(8, 3, 11, 'R');

SET IDENTITY_INSERT dbo.soccer_team OFF;
SET IDENTITY_INSERT dbo."match" OFF;
SET IDENTITY_INSERT dbo.foul OFF;

我可以做一些基本的事情，比如得到每支球队或每年的红牌犯规数量：

SELECT
    YEAR( match_date ) AS yearly_season,
    COUNT( foul_type ) AS total_red_cards 
FROM
    dbo.foul AS df
    RIGHT JOIN dbo.match AS dm ON
        df.match_id = dm.match_id
        AND
        df.foul_type = 'R'

GROUP BY
    YEAR( match_date )

和

SELECT
    team_name,
    COUNT( foul_type ) AS total_red_cards 

FROM
    dbo.foul AS df
    RIGHT JOIN dbo.soccer_team AS dst ON
        df.team_id = dst.team_id
        AND
        df.foul_type = 'R'

GROUP BY
    team_name

他们的表现很好，包括没有红色犯规的球队或年份。获得的结果如下：

Year|红牌总数

2018|0
2019|0
2020|4

和

Team|红牌总数

A队|0
B队|1
C队|3

但我无法得到以下是我的要求：

Year|Team|红牌总数
-|-|
2018|A队|0
2018|B队|0
2018|Team C|0
2019|A队|0
2019|B队|0
2019|Team C|0
2020|A队|0
2020|B队|1
2020|Team C|3

取而代之的是，我使用如下查询获得以下内容：

SELECT
    YEAR( match_date ) AS yearly_season,
    team_name, 
    COUNT( foul_type ) AS total_red_cards

FROM
    dbo.foul AS df
    INNER JOIN dbo.soccer_team AS dst ON
        df.team_id = dst.team_id
        AND
        df.foul_type = 'R'

    RIGHT JOIN dbo.match AS dm ON
        df.match_id = dm.match_id

GROUP BY
    YEAR( match_date ),
    team_name

Year|Team|红牌总数
-|-|
2018|空|0
2019|空|0
2020|空|0
2020|B队|1
2020|Team C|3

或
Year|Team|红牌总数
-|-|
2020|B队|1
2020|Team C|3

或者更糟糕的是，如果我使用从犯规开始一直到比赛的连接(数字不相加)：

Year|Team|红牌总数
-|-|
2018|空|0
2019|空|0
2020|空|0
2020|B队|3
2020|Team C|12

也许一些子查询可以完成这项工作？还是数据库结构有问题？

sql-server

来源：https://stackoverflow.com/questions/73914647/getting-number-of-fouls-per-team-per-year-for-soccer

2条答案

按热度按时间

cu6pst1q1#

这里有一个方法可以做到这一点。对于这样的情况，这不是您想要返回的所有结果的数据点，您必须首先构建所需的结果集-在本例中使用团队和年份的CROSS JOIN。

然后，一旦您有了所需的行，您就可以使用子查询来获得红牌犯规。

SELECT Y.MatchYear, T.team_name
    , (
        SELECT COUNT(*)
        FROM #foul F
        INNER JOIN #match M ON M.match_id = f.match_id
        WHERE F.team_id = T.team_id AND YEAR(M.match_date) = Y.MatchYear AND F.foul_type = 'R'
    )
FROM #soccer_team T
CROSS JOIN (
    SELECT YEAR([match_date]) MatchYear
    FROM #match
    GROUP BY YEAR([match_date])
) Y
ORDER BY Y.MatchYear ASC, T.team_name ASC;

注意，我使用临时表是因为我不想让数据库中有永久性的表，所以只需更改表名即可。

赞(0）回复(0）举报 2022-10-03

ha5z0ras2#

戴尔的解决方案的替代方案(这是非常好的)。由于您想要计算不存在的行数(没有一年犯规的球队)，您需要以某种方式“构建”域(所有球队，所有年份)。CROSS JOIN是两个集合之间的笛卡尔乘积的SQL行话：

select *
from dbo.[match] m
cross join dbo.soccer_team t

我们称这种关系为x，因为没有更好的词：

from ( 
  select *
  from dbo.[match] m
  cross join dbo.soccer_team t
) as x

现在我们可以加入这个犯规，得到年份，球队名称和相关的卡片。请注意，我们需要外部加入，因为所有球队并不是每年都有犯规：

select year(x.match_date), x.team_name
     , f.foul_type
from ( 
  select *
  from dbo.[match] m
  cross join dbo.soccer_team t
) as x
left join dbo.foul f
    on f.match_id = x.match_id
    and f.team_id = x.team_id

我们可以在聚合函数中使用它，而不是在WHERE子句中添加筛选器。COUNT(x)计算所有非空的x。缺少的行数已经为空，因此我们需要处理‘R’以外的卡片

case when f.foul_type = 'R' then 1 end

会将除‘R’以外的所有FUAL_TYPEMap为NULL，因此它们不会被COUNT考虑：

select year(x.match_date), x.team_name
     , count(case when f.foul_type = 'R' then 1 end) 
from ( 
  select *
  from dbo.[match] m
  cross join dbo.soccer_team t
) as x
left join dbo.foul f
    on f.match_id = x.match_id
    and f.team_id = x.team_id
group by year(x.match_date), x.team_name
order by year(x.match_date), x.team_name

Fiddle我添加了戴尔解决方案的修改(删除#)版本

顺便说一句，改进你的帖子做得很好。如果没有DDL，我永远不会考虑回答这个问题。如果您想要进一步提高您未来的帖子，您可以添加db<>fiddle

赞(0）回复(0）举报 2022-10-03

我来回答

获得每支球队每年的足球犯规次数

2条答案

相关问题

热门标签

最新问答