douxian4376 2017-07-20 16:20
浏览 80
已采纳

对于Left Join和Group By,MYSQL COUNT不返回0

I am simply trying to get the code below to recognize when the count is zero and return 0.

Setting the date variables and week numbers table

//set first of the year and today
  $year = date("Y");
  $start = "01/01/".$year;
  $today = date("Y-m-d");
  $first = $year."-01-01";
  $start = $year."-01-01";

  $date1 = new DateTime($first);
  $date2 = new DateTime($today);
  $interval = $date1->diff($date2);

  $weeks = floor(($interval->days) / 7);

  //create weeks table
    $sql = "DROP TEMPORARY TABLE IF EXISTS weekstbl" ;
    mysqli_query ($db, $sql ) or ( "Error " . mysqli_error () ) ;

    $weekstbl = "
      CREATE TEMPORARY TABLE weekstbl (
        `weekNo` int NOT NULL,
        `weekStart` Date,
        `weekEnd` Date,
        PRIMARY KEY(weekNo)
      )
    ";

   mysqli_query($db, $weekstbl) or die ("Sql error : ".mysqli_error());

  for($i = 1; $i <= $weeks; $i++){    
      $week = $date1->format("W");
      $date1->add(new DateInterval('P6D'));
      $date1->format('Y-m-d');
      $newDate1 = $date1->format('Y-m-d');

      $statement = $db->prepare("INSERT INTO weekstbl (weekNo,weekStart,weekEnd) VALUES (?,?,?)");
      $statement->bind_param('iss', $week,$start,$newDate1);
      $statement->execute();

      $date1->add(new DateInterval('P1D'));
      $start = $date1->format('Y-m-d');
  }

This code outputs the all of the week numbers, start date for the week and end date for the week for the current year through the current day. Looks like the following:

------------------------------------
| weekNo |  weekStart |  weekEnd   |
------------------------------------
|   52   | 2017-01-01 | 2017-01-07 |
|    1   | 2017-01-08 | 2017-01-14 |
|    2   | 2017-01-15 | 2017-01-21 |

This continues on through the current date the system is being accessed. This will then be used to join with another few other tables with conditions. This is what I expect to be returned but in all of my trials listed below, I cannot accomplish this. I ran this when there were 12 weeks in the current Year, which means there should be 12 numbers following the name.

[["Ryan Balcom",3,30,3,1,10,0,1,2,5,1,3,3],["Jared Beckwith",40,8,13,8,13,7,5,3,11,5,3,1],["Jim Roberts",0,32,8,7,2,9,4,2,8,4,4,10],["Jim Kelly",44,24,12,14,14,16,10,25,12,8,7,6],["Josh Bell",34,10,10,5,9,8,5,23,7,6,6,14],["Rick Zuniga",0,0,0,0,0,3,1,1,0,0,0,0],["Mike Horton",37,20,7,10,16,5,6,4,5,3,5,1],["Paul Schilthuis",31,11,9,2,8,6,4,0,5,9,8,4],["TJ Sutton",1,10,0,0,0,0,0,0,0,0,0,0]]

Trial 1

    $assignments = "
SELECT COUNT(j.leadid) AS leadcount, u.username
  FROM weekstbl wk
  LEFT JOIN jobbooktbl j
    ON wk.weekNo=WEEK(j.leadcreated)
  LEFT JOIN assignmentstbl a
    ON j.leadid=a.custid
  LEFT JOIN usertbl u
    ON a.userid=u.userid
  WHERE j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND YEAR(j.leadcreated) = '".$year."'
  GROUP BY WEEK(j.leadcreated), a.userid";
    $assignmentsqry = mysqli_query($db,$assignments);
    while ($row = mysqli_fetch_array($assignmentsqry)) {
      $key = $row['username']; //unnecessary variable for demonstration purposes
      if (!isset($volumeYTDsm[$key])) {
        $volumeYTDsm[$key] = [$row['username']];
      }
      $float = floatval($row['leadcount']);
        $volumeYTDsm[$key][] = $float;
      }
      $volumeYTDsm = array_values($volumeYTDsm);//removing keys
    }

This outputs the above but does not inlcude the 0 weeks:

[["Ryan Balcom",3,30,3,1,10,1,2,5,1,3,3],["Jared Beckwith",40,8,13,8,13,7,5,3,11,5,3,1],["Jim Roberts",32,8,7,2,9,4,2,8,4,4,10],["Jim Kelly",44,24,12,14,14,16,10,25,12,8,7,6],["Josh Bell",34,10,10,5,9,8,5,23,7,6,6,14],["Rick Zuniga",3,1,1],["Mike Horton",37,20,7,10,16,5,6,4,5,3,5,1],["Paul Schilthuis",31,11,9,2,8,6,4,5,9,8,4],["TJ Sutton",1,10]]

Trial 2

$assignments = "
  SELECT COUNT(*) AS leadcount, u.username
    FROM weekstbl wk
    LEFT OUTER JOIN jobbooktbl j
      ON (wk.weekNo=WEEK(j.leadcreated)) AND j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND YEAR(j.leadcreated) = '".$year."'
    LEFT JOIN assignmentstbl a
      ON j.leadid=a.custid
    LEFT JOIN usertbl u
      ON a.userid=u.userid
    GROUP BY wk.weekNo, a.userid";

This outputs the following...not really sure what this is outputting with the null name:

[[null,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1],["Ryan Balcom",3,30,3,1,10,1,2,5,1,3,3],["Jared Beckwith",40,8,13,8,13,7,5,3,11,5,3,1],["Jim Roberts",32,8,7,2,9,4,2,8,4,4,10],["Jim Kelly",44,24,12,14,14,16,10,25,12,8,7,6],["Josh Bell",34,10,10,5,9,8,5,23,7,6,6,14],["Rick Zuniga",3,1,1],["Mike Horton",37,20,7,10,16,5,6,4,5,3,5,1],["Paul Schilthuis",31,11,9,2,8,6,4,5,9,8,4],["TJ Sutton",1,10]]

With both of these methods I have tried to include COALESCE and IFNULL but neither changed the result for either query:

COALESCE(COUNT(j.leadid),0) AS leadcount
IFNULL(COUNT(j.leadid),0) AS leadcount

I have been trying to figure this out for a month and nothing I throw at it seems to work. Any assistance or direction would be greatly appreciated!

  • 写回答

2条回答 默认 最新

  • douyuai8994 2017-07-20 16:28
    关注

    The condition in the WHERE is turning the first LEFT JOIN into an inner join. So, you would seem to want:

    SELECT COUNT(j.leadid) AS leadcount, u.username
    FROM weekstbl wk LEFT JOIN
         jobbooktbl j
         ON wk.weekNo = WEEK(j.leadcreated) AND
            j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND
            YEAR(j.leadcreated) = '".$year."'LEFT JOIN
         assignmentstbl a
         ON j.leadid = a.custid LEFT JOIN
         usertbl u
         ON a.userid = u.userid
    GROUP BY WEEK(j.leadcreated), a.userid;
    

    However, you are aggregating by the week, but not including the week in the SELECT. I suspect that you want:

    SELECT wk.weekNo, u.username, COUNT(j.leadid) AS leadcount
    FROM weekstbl wk LEFT JOIN
         jobbooktbl j
         ON wk.weekNo = WEEK(j.leadcreated) AND
            j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND
            YEAR(j.leadcreated) = '".$year."'LEFT JOIN
         assignmentstbl a
         ON j.leadid = a.custid LEFT JOIN
         usertbl u
         ON a.userid = u.userid
    GROUP BY wk.weekNo, a.userid;
    

    You don't want to aggregate by WEEK(j.leadcreated) because that might be NULL.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗