mysql按子查询结果过滤

时间:2022-01-28 20:34:44

i have this Query :

我有这个查询:

SELECT a.*, a.id AS id_player, (SELECT COUNT(id) FROM `vd7qw_footsal_goals` WHERE a.id = id_player AND g.id = id_group) AS goals, 
team.* FROM `vd7qw_footsal_players` AS a 

LEFT JOIN vd7qw_footsal_teams AS team ON team.id= a.id_team

LEFT JOIN vd7qw_footsal_teamofgroup AS tog ON tog.id_team = team.id 4

LEFT JOIN vd7qw_footsal_groups AS g ON g.id = tog.id_group
WHERE g.id IN (SELECT id_group from `vd7qw_footsal_groupofleague` WHERE id_league = 2) 
AND (a.state IN (1)) AND goals > 0 ORDER BY goals DESC

and i want to filter its resaults by players that have goals

而我想通过有目标的球员过滤其击球

the above query have error in this part goals > 0 i don't know how to do that can any1 help me ?

上面的查询在这部分目标中有错误> 0我不知道怎么办可以帮助我吗?

1 个解决方案

#1


3  

Try this:

SELECT * FROM 
(SELECT a.*, a.id AS id_player, (SELECT COUNT(id) FROM `vd7qw_footsal_goals` WHERE a.id = id_player AND g.id = id_group) AS goals, 
team.* FROM `vd7qw_footsal_players` AS a 

LEFT JOIN vd7qw_footsal_teams AS team ON team.id= a.id_team

LEFT JOIN vd7qw_footsal_teamofgroup AS tog ON tog.id_team = team.id 4

LEFT JOIN vd7qw_footsal_groups AS g ON g.id = tog.id_group
WHERE g.id IN (SELECT id_group FROM `vd7qw_footsal_groupofleague` WHERE id_league = 2) 
AND (a.state IN (1))) AS A WHERE goals > 0 ORDER BY goals DESC

#1


3  

Try this:

SELECT * FROM 
(SELECT a.*, a.id AS id_player, (SELECT COUNT(id) FROM `vd7qw_footsal_goals` WHERE a.id = id_player AND g.id = id_group) AS goals, 
team.* FROM `vd7qw_footsal_players` AS a 

LEFT JOIN vd7qw_footsal_teams AS team ON team.id= a.id_team

LEFT JOIN vd7qw_footsal_teamofgroup AS tog ON tog.id_team = team.id 4

LEFT JOIN vd7qw_footsal_groups AS g ON g.id = tog.id_group
WHERE g.id IN (SELECT id_group FROM `vd7qw_footsal_groupofleague` WHERE id_league = 2) 
AND (a.state IN (1))) AS A WHERE goals > 0 ORDER BY goals DESC