代码之家 › 专栏 › 技术社区 › treyBake user1850175

MySQL SUM返回意外值

sql mysql

treyBake user1850175 · 技术社区 · 6 年前

我一直想用 SUM() 合计数据库中的一些数值,但它似乎返回了意外的值。以下是用于生成SQL的PHP端:

public function calcField(string $field, array $weeks)
{
    $stmt = $this->conn->prepare('SELECT SUM(`'. $field .'`) AS r FROM `ws` WHERE `week` IN(?);');
    $stmt->execute([implode(',', $weeks)]);

    return $stmt->fetch(\PDO::FETCH_ASSOC)['r'];
}

让我们为家里的各位提供一些示例数据:

$field = 'revenue';
$weeks = [14,15,16,17,18,19,20,21,22,23,24,25,26];

返回以下值:

4707.92

在看不到数据的情况下,这似乎奏效了,但这几周的情况如下:

+----+------+------+---------+-------+---------+---------+------+----------+
| id | week | year | revenue | sales | gpm_ave | uploads | pool | sold_ave |
+----+------+------+---------+-------+---------+---------+------+----------+
|  2 |   14 | 2019 | 4707.92 |   292 |      13 |       0 | 1479 |       20 |
|  3 |   15 | 2019 | 4373.32 |   304 |      13 |       0 | 1578 |       19 |
|  4 |   16 | 2019 | 4513.10 |   275 |      14 |       0 | 1460 |       19 |
|  5 |   17 | 2019 | 4944.80 |   336 |      14 |       0 | 1642 |       20 |
|  6 |   18 | 2019 | 4343.87 |   339 |      13 |       0 | 1652 |       21 |
|  7 |   19 | 2019 | 3918.59 |   356 |      14 |       0 | 1419 |       25 |
|  8 |   20 | 2019 | 4091.20 |   247 |      19 |       0 | 1602 |       15 |
|  9 |   21 | 2019 | 4177.22 |   242 |      12 |       0 | 1588 |       15 |
| 10 |   22 | 2019 | 3447.88 |   227 |      18 |       0 | 1585 |       14 |
| 11 |   23 | 2019 | 3334.18 |   216 |      15 |       0 | 1675 |       13 |
| 12 |   24 | 2019 | 4736.15 |   281 |      13 |       0 | 1388 |       20 |
| 13 |   25 | 2019 | 4863.84 |   252 |      12 |       0 | 1465 |       17 |
| 14 |   26 | 2019 | 4465.95 |   281 |      21 |       0 | 1704 |       16 |
+----+------+------+---------+-------+---------+---------+------+----------+

如你所见,总数应该远远大于 4707.92 4707.92 .

如果我在函数中添加以下内容,事情会变得很奇怪:

echo 'SELECT SUM(`'. $field .'`) AS r FROM `ws` WHERE `week` IN('. implode(',', $weeks) .');';

输出:

SELECT SUM(revenue) AS r FROM ws WHERE week IN(14,15,16,17,18,19,20,21,22,23,24,25,26);

MariaDB [nmn]> SELECT SUM(revenue) AS r FROM ws WHERE week IN(14,15,16,17,18,19,20,21,22,23,24,25,26);
+----------+
| r        |
+----------+
| 55918.02 |
+----------+
1 row in set (0.00 sec)

看起来更准确。然而,同样的SQL语句返回第一行值,而不是这几周的列总和。

此函数由AJAX脚本触发:

$d = new Page\Snapshot\D();

# at the minute only outputting dump of values to see what happens
echo '<pre>'. print_r(
        $d->getQuarterlySnapshot(new Page\Snapshot\S(), new App\Core\Date(), $_POST['quarter'], '2019'),
        1
    ). '</pre>';

函数 $d->getQuarterlySnapshot 功能:

public function getQuarterlySnapshot(S $s, Date $date, int $q, string $year)
{
    switch($q)
    {
        case 1:
            $start = $year. '-01-01 00:00:00';
            $end = $year. '-03-31 23:59:59';
            break;
        case 2:
            $start = $year. '-04-01 00:00:00';
            $end = $year. '-06-30 23:59:59';
            break;
        case 3:
            $start = $year. '-07-01 00:00:00';
            $end = $year. '-09-30 23:59:59';
            break;
        case 4:
            $start = $year. '-10-01 00:00:00';
            $end = $year. '-12-31 23:59:59';
            break;
    }

    $weeks = $date->getWeeksInRange('2019', 'W', $start, $end);
    foreach ($weeks as $key => $week){$weeks[$key] = $week[0];}

    return [
        'rev' => $s->calcField('revenue', $weeks),
        'sales' => $s->calcField('sales', $weeks),
        'gpm_ave' => $s->calcField('gpm_ave', $weeks),
        'ul' => $s->calcField('uploads', $weeks),
        'pool' => $s->calcField('pool', $weeks),
        'sold_ave' => $s->calcField('sold_ave', $weeks)
    ];
}

所以我不会覆盖任何地方的值(至少我可以看到)。如何使用 总和() IN() 有条件的?

2 回复 | 直到 6 年前

treyBake user1850175 6 年前

正如秘书长在评论中指出的那样 :

逗号分隔的week\u id值在查询中作为单个字符串传递:week\u id in('1,2,3…,15')。MySQL隐式地将其类型转换为1,因此只获取第一行。您需要更改查询准备代码

导致我分手了 ?

public function calcField(string $field, array $weeks)
{
    $data = [];
    $endKey = end(array_keys($weeks));

    $sql = 'SELECT SUM(`'. $field .'`) AS r FROM `ws` WHERE `week` IN(';

    foreach ($weeks as $key => $week)
    {
        $sql .= ':field'. $key;
        $sql .= ($key !== $endKey ? ',' : '');

        $data[':field'. $key] = $week;
    }

    $sql .= ');';

    $stmt = $this->conn->prepare($sql);
    $stmt->execute($data);

    return $stmt->fetch(\PDO::FETCH_ASSOC)['r'];
}

现在,每个数值都被单独处理,这就得到了期望值。

Ishpreet 6 年前

where week IN (1,2,3,4,5,6,7,8,9,10,11,12,13); 你的数据显示你有第13周,而第1到12周的数据中不存在 IN

Fubarotoko 6 年前

您只选择了第13周的行,数据中不存在第1-12周。这就是为什么你的查询结果是 43.900001525878906

解决方案1:

IN() 因为您正在按列筛选数据 week

SELECT SUM(`revenue`) as `r` FROM `ws` WHERE `week` IN (13,14,15,16,17,18,19,20,21,22,23,24,25);

解决方案2:

你可以改变主意 周 在你的 where 从句至 id

SELECT SUM(`revenue`) as `r` FROM `ws` WHERE `id` IN (1,2,3,4,5,6,7,8,9,10,11,12,13);