时间戳

首页 > 解决方案 > 计算每个 ID 数量的比例

mysql - 计算每个 ID 数量的比例

问题描述

这是我的桌子。类型 0 用于dislike，类型 1 用于like。

id | userid | postid | type
1  |   465  |  20    |  0
2  |   465  |  21    |  1
3  |   466  |  20    |  1
4  |   466  |  21    |  0
5  |   467  |  20    |  1
6  |   467  |  21    |  0

现在，我的查询是

$sql = $bdd->query('SELECT COUNT(id) AS cntLikes,  postid, type FROM like_unlike WHERE type = 1 GROUP BY postid, type');
$sql = $bdd->query('SELECT COUNT(id) AS cntUnlikes,  postid, type FROM like_unlike WHERE type = 0 GROUP BY postid, type');

这向我显示：

postid(20) -> 1 like 
postid(21) -> 2 like1

postid(20) -> 2 dislikes
postid(21) -> 1 dislike

我想计算 %like并为每个postid.

$p = ($total_unlikes / ($total_unlikes+$total_likes)) * 100;

我试过了

$sql = $bdd->query("SELECT postid, COUNT(CASE type WHEN '1' THEN 1 END) AS cntLikes, COUNT(CASE type WHEN '0' THEN 1 END) AS cntunlikes from like_unlike GROUP BY postid, type");
    while ($donnees2 = $sql->fetch())  {
    $total_likes = $donnees2['cntLikes'];
    $total_unlikes = $donnees2['cntunlikes'];
    $ratio = ($total_unlikes / ($total_unlikes+$total_likes)) * 100;
    echo $donnees2['postid'],'  nb likes',$total_likes,' nb dislikes',$total_unlikes,'   percent',$p,'<br />';}

但它显示给我

postid(20)  -> nb likes 1 -> nb dislikes 0 -> percent 100
postid(20)  -> nb likes 0 -> nb dislikes 2 -> percent ->100
postid(21)  -> nb likes 2 -> nb dislikes 0 -> percent -> 100
postid(21)  -> nb likes 0 -> nb dislikes -> 1  percent ->100

以下是我想要的最终结果：

postid(20)  -> nb likes 1 -> nb dislikes 2 -> percent 33
postid(21)  -> nb likes 2 -> nb dislikes 1 -> percent ->66

标签： mysql

解决方案

type从, 中删除GROUP BY，这样您就不会为每种类型获得一组（和一行），而是为两种类型获得一组。

推荐阅读