php - 防止将行字段值重复到下一行

Question

我有一个查询，它应该从不同的表中加入不同类型的用户奖金，并每周总结奖金。不同类型奖金的总金额处理得很好，但问题是当用户没有获得任何奖金时，我将值带到下一个。

这是我的查询的样子：

$bonuses =DB::table('users as u')
      ->select(
        'u.id',
        'u.referral',
        'packages.name as package_name',
        'shoppings.week_number as shopping_week',
        'performances.week_number as performance_week',
        'spillovers.week_number as spillover_week',
        'leaderships.week_number as leadership_week',
        DB::raw("CONCAT(u.first_name,' ',u.last_name) AS full_name"),
        DB::raw('ifnull(sum(shoppings.amount),0) as shopping_amount'),
        DB::raw('ifnull(sum(performances.amount),0) as performance_amount'),
        DB::raw('ifnull(sum(spillovers.amount),0) as spillover_amount'),
        DB::raw('ifnull(sum(leaderships.amount),0) as leadership_amount'),
        DB::raw("ifnull(sum(shoppings.amount),0) +
        ifnull(sum(performances.amount),0) +
        ifnull(sum(spillovers.amount),0) +
        ifnull(sum(leaderships
        .amount),0)
        as total"),
      )
      ->leftJoin('packages', 'u.package_id', '=', 'packages.id')
      ->leftJoin('shoppings', 'u.id', '=', 'shoppings.user_id')
      ->leftJoin('performances', 'u.id', '=', 'performances.user_id')
      ->leftJoin('spillovers', 'u.id', '=', 'spillovers.user_id')
      ->leftJoin('leaderships', 'u.id', '=', 'leaderships.user_id')
      ->groupBy('shopping_week', 'performance_week', 'spillover_week', 'leadership_week')
      ->get();
    return $bonuses;

是一个屏幕截图，显示它是如何呈现的。带圆圈的字段应该是0但正在采用前一行的值。

我真的不知道我做错了什么。提前致谢

我加入的表的架构：

public function up()
  {
    Schema::create('shoppings', function (Blueprint $table) {
      $table->id();
      $table->float('amount', 10, 2)->default(0);
      $table->boolean('paid')->default(0);
      $table->foreignId('user_id');
      $table->foreignId('downline_id');
      $table->integer('week_number')->nullable();
      $table->timestamps();
    });
  }


public function up()
  {
    Schema::create('performances', function (Blueprint $table) {
      $table->id();
      $table->float('amount', 10, 2)->default(0);
      $table->boolean('paid')->default(0);
      $table->foreignId('user_id');
      $table->foreignId('downline_id');
      $table->integer('week_number')->nullable();
      $table->timestamps();
    });
  }

public function up()
  {
    Schema::create('spillovers', function (Blueprint $table) {
      $table->id();
      $table->float('amount', 10, 2)->default(0);
      $table->boolean('paid')->default(0);
      $table->foreignId('user_id');
      $table->foreignId('downline_id');
      $table->integer('week_number')->nullable();
      $table->timestamps();
    });
  }

public function up()
  {
    Schema::create('leaderships', function (Blueprint $table) {
      $table->id();
      $table->float('amount', 10, 2)->default(0);
      $table->boolean('paid')->default(0);
      $table->foreignId('user_id');
      $table->foreignId('downline_id');
      $table->integer('week_number')->nullable();
      $table->timestamps();
    });
  }

标签： phpmysqllaravel