主页 / 问题 / 1330574
Accepted
Igor
Asked:2022-09-21 14:51:51 +0000 UTC2022-09-21 14:51:51 +0000 UTC

如何构建一个查询来计算评级表中的每个值？

  • 772

有一个ratings五星级评级表，其中包含以下列值：

Schema::create('ratings', function (Blueprint $table) {
        $table->bigIncrements('id')->unsigned(); //автоинкремент
        $table->integer('id_post')->unsigned(); // id публикации
        $table->integer('id_user')->unsigned()->nullable(); // id пользователя
        $table->unsignedTinyInteger('rating'); // рейтинг, со значением от 1 до 5
    });

现在我正在做一个简单的计算并使用这样的查询输出平均值：

select
  AVG(rating) as avg,
  COUNT(*) as amount
from
  `ratings`
where
  `id_post` = '1'

结果我得到：

$rating = [
    'avg' => '3.400',
    'amount' => 10
];

问题：如何构建一个额外计算的查询以及每颗星的数量？也就是有多少人打1星，多少人打2星等。所以结果会是这样的：

$rating = [
        'avg' => '3.400',
        'amount' => 10,
        'star_count1' => 2,
        'star_count2' => 1,
        'star_count3' => 1,
        'star_count4' => 3,
        'star_count5' => 3
    ];
mysql

1 个回答

  1. Best Answer

    您可以将该函数COUNT与条件运算符一起使用IF来仅计算某些值。

    示例：

    SELECT
  AVG(rating) as `avg`,
  COUNT(*) as `amount`,
  COUNT(IF(`rating` = 1, 1, NULL)) AS `star_count1`,
  COUNT(IF(`rating` = 2, 1, NULL)) AS `star_count2`,
  COUNT(IF(`rating` = 3, 1, NULL)) AS `star_count3`,
  COUNT(IF(`rating` = 4, 1, NULL)) AS `star_count4`,
  COUNT(IF(`rating` = 5, 1, NULL)) AS `star_count5`
FROM
  `ratings`
WHERE
  `id_post` = '1'
    • 1

相关问题