Question

我的桌子基本是足球赛的日历切入。

team1 || team1score || team2|| team2score || time || league

我想根据日历表中的条目更新我的立场表。

so for example if I have in my table something like

Pats   || 14 || Saints || 3  || 1stleague
Cows   || 7  || falc   || 14 || 1stleague
Saints || 31 || falc   || 3  || 1stleague
Saints || 14 || cows   || 3  || 1stleague

我要问的是,首先将一组独特的小组1 名称的小组1 记分栏的所有数值汇总起来。因此,就上述表格而言,它将因此而提供。

Pats   || 14
Cows   || 7
Saints || 45

then I want to do the same thing for team2 and team2score so the result would be

Saints || 3
falc   || 17
cows   || 3

然后,我要加入这两项成果,并取得:

Pats   || 14
Cows   || 10
Saints || 48
falc   || 17

我试图这样做。

select distinct(t.team1), sum(t.team1score)
  from calendar t
  where league =  5a7 
  UNION
    select distinct(t2.team2), sum(t2.team2score)
    from calendar t2
    where league =  5a7

但它没有增加同一个小组,而是把任何想法都归为核心?

我知道,当我做工会时,我不拿钱,但我认为,我需要做工会,把没有相应团队的价值观保留在小组2栏。然而,我可以说明如何同时实现工会和总合。

Answer 1

Use a sub-query to create a union of the team1 and team2 columns, aggregate the scores (only) in the outer query:

SELECT LOWER(t.team) team, SUM(t.score) score
FROM (
    SELECT team1 team, team1score score
    FROM calendar
    WHERE league= 5a7 
    UNION ALL
    SELECT team2, team2score
    FROM calendar
    WHERE league= 5a7 
) t
GROUP BY 1

<><>Edit>: 在一份<条码>UNION中,工会之后的选定声明的名称/内容被忽略。如果你认为改进可读性,可添加<条码>team和<条码><核心>。然而,不需要。

<><>Edit>: 添加<条码>LOWER,即对集团案件不敏感。

友情链接