php-获取2个数组之间的差

我有2个搜索查询-一个将显示最近7天的内容.另一个将显示2周前的内容.
两者都很好.但是我想从第一个查询中获取结果,并从第二个查询中获取差异.然后显示带有差异的第一个查询.

$result_account = $db->query("
SELECT nid,COUNT(cat) AS qty,dte,descript,cat,name,user 
  FROM client_note AS cn 
  JOIN client_note_tag_items AS cnti 
    ON cnti.note_id = cn.nid 
  JOIN client_note_tags AS cnt 
    ON cnt.tag_id = cnti.tag_id 
 WHERE dte >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) 
   AND name NOT LIKE 'Resolution%' 
 GROUP 
    BY cat 
 ORDER 
    BY qty DESC 
 LIMIT 5
");
       if($count_account = $result_account->num_rows) {
               while($row = $result_account->fetch_object()){

          echo "<tr>";
          echo "<td><h6>".$row->cat."</h6></td><td><h3 class='text-primary'>".$row->qty."</h3></td>";
          echo "</tr>";
          }
       }

$result_prevIoUs = $db->query("SELECT nid,user FROM client_note AS cn JOIN client_note_tag_items AS cnti ON cnti.note_id = cn.nid JOIN client_note_tags AS cnt ON cnt.tag_id = cnti.tag_id WHERE (dte BETWEEN DATE_SUB(CURDATE(),INTERVAL 21 DAY) AND DATE_SUB(CURDATE(),INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
    if($count_prevIoUs = $result_prevIoUs->num_rows) {
            while($row_p = $result_prevIoUs->fetch_object()){

          echo "<tr>";  
          echo "<td><h6>".$row_p->cat."</h6></td><td><h3 class='text-primary'>".$row_p->qty."</h3></td>";
          echo "</tr>";


            }
    }

第一个查询将导致：

Category   - Qty
Baseball   - 45
Football   - 33
Soccer     - 21
Hockey     - 7
Basketball - 3

第二个查询将导致：

Category   - Qty
Basketball - 38
Soccer     - 28
Hockey     - 16
Football   - 12
Baseball   - 12

现在我要这样显示

Category   - Qty Difference
Baseball   - 45  +33
Football   - 33  +21
Soccer     - 21  -7
Hockey     - 7   -9
Basketball - 3   -35

最佳答案

我要做的是创建另一个数组,该数组在键-值的基础上保存来自第一个查询的值,而在下一个查询中,仅从数组中相应的条目中减去值.

我认为这是使用2个查询的最简单,最快的方法.

注意：我没有测试此代码.

注意2：我假设第一类运动缺失将被计为0

注意3：我编写的代码仅用于显示计算差异的方法.让我知道是否应该更新它,以提供您在那里所需的完全相同的输出.

$results = array();

$result_account = $db->query("SELECT nid,user FROM client_note AS cn JOIN client_note_tag_items AS cnti ON cnti.note_id = cn.nid JOIN client_note_tags AS cnt ON cnt.tag_id = cnti.tag_id WHERE `dte` >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
   if($count_account = $result_account->num_rows) {
      while($row = $result_account->fetch_object()){

        //output this query results here
        $results[$row->cat] = $row->qty;
      }
   }

$result_prevIoUs = $db->query("SELECT nid,INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
if($count_prevIoUs = $result_prevIoUs->num_rows) {
    while($row_p = $result_prevIoUs->fetch_object()){

      //output this query results here
      $results[$row_p->cat] = ((isset($results[$row_p->cat])) ? $results[$row_p->cat] : 0 ) - $row_p->qty;
    }
}


foreach( $results as $key => $result) {
  echo "<tr>";  
  echo "<td><h6>".$key."</h6></td><td><h3 class='text-primary'>".$result."</h3></td>";
  echo "</tr>";
}

更新-显示第一周及其旁边的差异

你可以试试看我摆脱了foreach,仅使用查询循环来完成所有操作.

注意：同样,这是未经测试的代码

$results = array();

$result_account = $db->query("SELECT nid,INTERVAL 7 DAY) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");

$result_prevIoUs = $db->query("SELECT nid,INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC");
if($count_prevIoUs = $result_prevIoUs->num_rows) {
    while($row_p = $result_prevIoUs->fetch_object()){

      //output this query results here
      $results[$row_p->cat] = $row_p->qty;
    }
}

if($count_account = $result_account->num_rows) {
    while($row = $result_account->fetch_object()){

        $difference = $row->qty - ((isset($results[$row->cat])) ? $results[$row->cat] : 0 );

        echo "<tr>";  
        echo "<td><h6>".$row->cat."</h6></td><td><h3 class='text-primary'>".$row->qty."</h3></td><td><h3 class='text-primary'>".$difference."</h3></td>";
        echo "</tr>";
    }
}

原文链接：https://www.f2er.com/mysql/531908.html

php-获取2个数组之间的差

猜你在找的MySQL相关文章