select u.username, s.campaignno, if(f.hometeamscore>f.awayteamscore,1,0) -6ren">
gpt4 book ai didi

来自两个连接查询的 MySQL "Group By"

转载 作者:行者123 更新时间:2023-11-28 23:25:06 24 4
gpt4 key购买 nike

我生成了下面的查询,它返回了以下结果:

mysql> select u.username, s.campaignno, if(f.hometeamscore>f.awayteamscore,1,0) as Win, f.hometeamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.hometeamid and s.user_id = u.id union all
-> select u.username, s.campaignno, if(f.awayteamscore>f.hometeamscore,1,0) as Win, f.awayteamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.awayteamid and s.user_id = u.id;
+------------+------------+-----+-------+
| username | campaignno | Win | Goals |
+------------+------------+-----+-------+
| tingeyal | 34910256 | 1 | 5 |
| shanu | 35410256 | 1 | 4 |
| tingeyal | 34910256 | 0 | 0 |
| kOS | 35210256 | 1 | 2 |
| kOS | 35210256 | 0 | 0 |
| shanu | 35410256 | 1 | 3 |
| kriste8403 | 35510256 | 1 | 3 |
| kriste8403 | 35510256 | 0 | 0 |
+------------+------------+-----+-------+
8 rows in set (0.00 sec)

但是,我希望只为每个用户名和广告系列返回一行,而不是汇总“赢得”和“目标”列的组合。在上述实例中,我希望查询返回以下内容:

+------------+------------+-----+-------+
| username | campaignno | Win | Goals |
+------------+------------+-----+-------+
| tingeyal | 34910256 | 1 | 5 |
| shanu | 35410256 | 2 | 7 |
| kOS | 35210256 | 1 | 2 |
| kriste8403 | 35510256 | 1 | 3 |
+------------+------------+-----+-------+
4 rows in set (0.01 sec)

我相信这会涉及 group by?或者,也许我必须创建一个新查询并将其与这个查询结合起来。如您所见,我不确定下一步应该做什么。如果有人认为提供表格详细信息会有所帮助,请告诉我。

非常感谢。

最佳答案

I believe this will involve the group by?

是的,会的。要获得所需的结果,您不必编写单独的查询。尝试以下。

SELECT tablea.username, tablea.campaignno, SUM(tablea.Win) Win, SUM(tablea.Goals) Goals 
FROM
(select u.username, s.campaignno, if(f.hometeamscore>f.awayteamscore,1,0) as Win, f.hometeamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.hometeamid and s.user_id = u.id
union all
select u.username, s.campaignno, if(f.awayteamscore>f.hometeamscore,1,0) as Win, f.awayteamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.awayteamid and s.user_id = u.id) AS tablea
WHERE 1
GROUP BY tablea.username

以上查询将创建一个临时表tablea,其中包含两个子查询的数据并获取所需的数据。

关于来自两个连接查询的 MySQL "Group By",我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39696509/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com