计算中的用户变量

Question

我正在为高速公路车队制作一个排名表，我有一个查询，根据车队结果计算所需数据。我正在努力解决的问题是分数差异（得分和对手得分之间的差异）。

    SELECT tbl_clubs.club, tbl_clubs.club_id,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away AND tbl_fixtures.awayscore is not null ,1,0)) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.home AND tbl_fixtures.homescore is not null,1,0 )) as `M`,
SUM( if( tbl_clubs.club_id = tbl_fixtures.home
AND tbl_fixtures.homescore > tbl_fixtures.awayscore, 1, 0 ) ) AS `W`,
SUM( IF( tbl_clubs.club_id = tbl_fixtures.home AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 1, 0 ) ) AS `HD`,
SUM( if( tbl_clubs.club_id = tbl_fixtures.home
AND tbl_fixtures.homescore < tbl_fixtures.awayscore, 1, 0 ) ) AS `HL`,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore >=7,1,0)) AS `4W`,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore <=6,1,0)) AS `3W`,
SUM( IF( tbl_clubs.club_id = tbl_fixtures.away AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 1, 0 ) ) AS `AD`,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore < tbl_fixtures.homescore
AND tbl_fixtures.homescore - tbl_fixtures.awayscore <=6,1,0)) AS `1L`,

SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore < tbl_fixtures.homescore
AND tbl_fixtures.homescore - tbl_fixtures.awayscore >=7,1,0)) AS `L`,

@FOR:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.homescore,0)) as `F`,


@Against:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.homescore,0)) as `A`,

SUM(@For - @Against) as `PtsDiff`,

SUM( if( tbl_clubs.club_id = tbl_fixtures.home
AND tbl_fixtures.homescore > tbl_fixtures.awayscore, 3, 0 ) ) +
SUM( IF( tbl_clubs.club_id = tbl_fixtures.home AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 1, 0 ) ) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore >=7,4,0)) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore <=6,3,0)) +
SUM( IF( tbl_clubs.club_id = tbl_fixtures.away AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 2, 0 ) ) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore < tbl_fixtures.homescore
AND tbl_fixtures.homescore - tbl_fixtures.awayscore <=6,1,0))  as `Pts`
FROM tbl_clubs
INNER JOIN tbl_fixtures ON tbl_clubs.club_id = tbl_fixtures.home

OR tbl_clubs.club_id = tbl_fixtures.away
where tbl_clubs.league_id = 3
GROUP BY tbl_clubs.club_id
order by Pts desc, PtsDiff desc, club asc

所有查询都正常工作，除了

@FOR:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.homescore,0)) as `F`,


@Against:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.homescore,0)) as `A`,

SUM(@For - @Against) as `PtsDiff`,

我得到的结果是空的，我猜这很简单，任何帮助都会很好

Answer 1

首先，您需要确保null值始终默认为0而不是null。这是脚本中可能发生的事情，因此您应该确保0是唯一可行的默认值。这应该在执行此查询之前发生，尽管理想情况下是这样。根据您的脚本，情况似乎并非如此，因此您可能希望确保您已经设计了数据库来满足这一要求。

其次，查询中的变量分配并不像您希望的那样简单。您最好使用子查询进行初始化：

@FOR:=(select IFNULL(SUM(tbl_fixtures.awayscore),0)  from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.away) +   
        (select IFNULL(SUM(tbl_fixtures.homescore),0) from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.home)
as `F`,
@Against:=(select IFNULL(SUM(tbl_fixtures.awayscore),0)  from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.home) +   
        (select IFNULL(SUM(tbl_fixtures.homescore),0) from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.away)
as `A`, 
SUM(@FOR - @Against) as `PtsDiff`,

不太优雅，但这应该行得通。请注意使用IFNULL来确保null转换为0。