php - Multiple LEFT JOINS with Multiple COUNT() 计算所有列

Question

我有 4 张 table 。

表组

| ID | NAME |
  1    Premium
  2    Silver

表用户

| ID | group_id | NAME |
  1       1      Serhan
  2       2      Farhat

表 user_statistics

| ID | user_id | TYPE |
  1       1      1
  2       2      0

table 票

| ID | user_id | VOTE |
  1       1      1
  2       2      0
  3       1      0

我创建了一个 sql 查询来检索同一组中的用户详细信息。成功了!然后我想检索任何已投票给用户的投票。我要数票。所以基本上我已经做了这个 sql 查询。

global $conn;
$res_groups = array();
$stmt = $conn->prepare("
    SELECT * FROM groups
");
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
while ($group = $stmt->fetch()){
    $groups = array();
    $groups['id'] = $group['id'];
    $groups['name'] = $group['name'];

    $user_arr = array();
    $stmts = $conn->prepare('
        SELECT l.*,
        (SELECT MAX(ls.date) from user_statistics ls WHERE ls.user_id = l.id GROUP BY ls.user_id) as ls_date,
        (SELECT SUM(IF(ls.type="0", ls.type, 0)) FROM user_statistics ls WHERE ls.user_id = l.id GROUP BY ls.user_id) as ls_us,
        (SELECT SUM(IF(ls.type="1", ls.type, 0)) FROM user_statistics ls WHERE ls.user_id = l.id  GROUP BY ls.user_id) as ls_uk,
        (SELECT COUNT(*) FROM user_statistics WHERE type="1" AND ls.user_id = l.id GROUP BY ls.user_id) as totals,
        (SELECT COUNT(*) FROM votes v WHERE v.vote=0 AND confirm=0 AND v.user_id = l.id) as badvote,
        (SELECT COUNT(*) FROM votes v WHERE v.vote=1 AND confirm=0 AND v.user_id = l.id) as goodvote
        FROM user l
        LEFT JOIN
        user_statistics ls on l.id = ls.user_id
        LEFT JOIN votes v on v.user_id = l.id
        WHERE l.group_id = '.$groups['id'].' AND status = 1
        GROUP BY l.id,ls.user_id
    ');
    $stmts->execute();
    $stmts->setFetchMode(PDO::FETCH_ASSOC);
    while ($usr = $stmts->fetch()){

        $totalvote=($usr['badvote']+$usr['goodvote']);
        if($totalvote>0){
            $badvote=bcdiv($usr['badvote']*100/$totalvote,1,2);
            $goodvote=bcdiv($usr['goodvote']*100/$totalvote,1,2);
        }else{
            $badvote=0;
            $goodvote=0;
        }
        $votes[] = array(
            "count" => $usr['badvote'],
            "percent" => $badvote
        );
        $votes[] = array(
            "count" => $usr['goodvote'],
            "percent" => $goodvote
        );

        $user_arr[] = array(
            "id" => $usr['id'],
            "group_id" => $usr['group_id'],
            "name" => $usr['name'],
            "votes_summary" => $votes
        );
    }
    $groups['list'] = $user_arr;
    $res_groups[] = $groups;
}

除非一件事，否则所有代码似乎都有效。 VOTE 始终返回以计算我的数据库 VOTES 中的所有列并将数据应用于我的所有用户。我想要的是根据投票类型 GOOD 或 BAD 获得每个用户获得多少票。

任何帮助都会很好。

Answer 1

查看您的代码，您可以重构查询，避免为每一行选择一个子查询，并在连接中使用两个带有 group by 的子查询。

您对坏票和好票也有相同的代码可能是您需要不同的代码来获得不同的值

    SELECT l.*
      , t1.ls_date
      , t1.ls_us
      , t1.ls_uk
      , t2.totals
      , t2.badvote
      , t2.goodvote
    FROM user l
    INNER JOIN  (
     SELECT ls.user_id
        , MAX(ls.date) ls_date
        , SUM(IF(ls.type="0", ls.type, 0)) ls_us
        , SUM(IF(ls.type="1", ls.type, 0)) ls_uk
     from user_statistics ls 
     GROUP BY ls.user_id 
    )  t1 on t1.user_id = l.id
    LEFT JOIN ( 
        SELECT v.user_id
        , sum( case when type="1" then 1 else 0 end ) totals
        /* these are the same */
        , sum ( case when v.vote=1 AND confirm=0  then 1 else 0 END ) badvote  
        /* these are the same */
        , sum ( case when v.vote=1 AND confirm=0  then 1 else 0 END ) goodvote 
      FROM votes v
    ) t2 ON t2.user_id  = l.id 
    WHERE l.group_id = '.$groups['id'].' 
    AND status = 1

并且您应该避免在 SQL 中使用 PHP var(您有 SQL 注入(inject)的风险)。为此，您应该查看您的数据库驱动程序以获取准备好的语句和绑定(bind)值，或者至少确保您正确清理了 php var 内容