gpt4 book ai didi

mysql - 为什么 mysql_fetch 的总和值加倍了?

转载 作者:行者123 更新时间:2023-11-29 01:12:57 25 4
gpt4 key购买 nike

我有一个 2 数据库。

1为game_jnship_user,2为game_jnship_equip,数据如下:

这是game_jnship_user

+-----+-----------------+
| ID | uid |
+-----+-----------------+
| 1 | 50 |
+-----------------------+

这是game_jnship_equip

+-----+------------+---------------+--------------+
| ID | uid | ebonus | etype |
+-----+------------+---------------+--------------+
| 1 | 50 | 100 | 1 |
| 2 | 50 | 10 | 1 |
| 3 | 50 | 120 | 2 |
+-------------------------------------------------+

我查询如下:

$_G['uid'] = '50';
$ushuxing = DB::query("SELECT sum(t2.ebonus) AS equipatk, sum(t3.ebonus) AS equipdef FROM ".DB::table('game_jnship_user')." t1 LEFT JOIN ".DB::table('game_jnship_equip')." t2 ON (t1.uid = t2.uid AND t2.etype = '1') LEFT JOIN ".DB::table('game_jnship_equip')." t3 ON (t1.uid = t3.uid AND t3.etype = '2') WHERE t1.uid = $_G[uid]");
$rushuxing = DB::fetch($ushuxing);

之后,我的输出数据如下:

$rushuxing[equipatk] = 110;
$rushuxing[equipdef] = 240;

为什么 $rushuxing[equipdef] 输出是 240 而不是 120

谢谢。

这是来自上面示例的原始 MySQL 查询:

SELECT sum(t2.ebonus) AS equipatk, sum(t3.ebonus) AS equipdef
FROM game_jnship_user t1
LEFT JOIN game_jnship_equip t2
ON (t1.uid = t2.uid AND t2.etype = '1')
LEFT JOIN game_jnship_equip t3
ON (t1.uid = t3.uid AND t3.etype = '2')
WHERE t1.uid = '50'

最佳答案

LEFT JOIN 操作中,etype = 2 的记录与 etype = 1 的 2 条记录相匹配,因此您得到 240 120。

您可以改用以下利用条件聚合的查询:

SELECT SUM(CASE WHEN etype = 1 THEN ebonus ELSE 0 END) AS equipatk,
SUM(CASE WHEN etype = 2 THEN ebonus ELSE 0 END) AS equipdef
FROM game_jnship_equip
WHERE uid = 50

GROUP BY uid 子句不是必需的,因为您正在为特定 uid 值执行聚合。

关于mysql - 为什么 mysql_fetch 的总和值加倍了?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36540322/

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