防止表中出现多个条目

我很困惑，并面临的问题，在获得输出像下面的图片需要输出

我有一个名为number\ u status的数据库表，在该表中，我用时间戳存储了number的在线和离线历史记录。我需要得到json响应来显示android应用程序中的数据，如上图所示。我可以得到如下函数

public function compareNumber($firstNumber, $secondNumber, $email, $date) {
        $response = array('code' => 0, 'error' => false);
        $endDate = date('Y-m-d', strtotime("+1 day", strtotime($date))) . " " . explode(" ", $date)[1];

        $stmt = $this->conn->prepare("SELECT id FROM user WHERE email = ?");
        $stmt->bind_param("s", $email);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows) {
            $user =  $result->fetch_assoc();            
            $user_id = $user['id'];
            $stmt->close();

            $response["received"]   = $date;
            $response["created_at"] = $date;
            $response["end"]        = $endDate;
            $response["code"]       = 1;

            $arr = $this->helperNumber($firstNumber, $date, $endDate, $user_id);
            $arr1 = $this->helperNumber($secondNumber, $date, $endDate, $user_id);

            $response["logs"] = array_merge($arr, $arr1);
        }

        return $response;
    }

    public function helperNumber($numberToSearch, $date, $endDate, $user_id) {
        $stmt = $this->conn->prepare("SELECT number, number_status, status_time FROM number_status WHERE number = ? AND  user_id=? AND status_time > ? AND status_time < ? ORDER BY status_time DESC");
            $stmt->bind_param("iiss", $numberToSearch, $user_id, $date, $endDate);
            $stmt->execute();
            $result = $stmt->get_result();

            $number = array();
            if ($result->num_rows) {
                $lastrow = null;
                $i = 0;
                while($row = $result->fetch_assoc()) {
                    if(!isset($number[$i]))
                        $number[$i] = array('number' => $row['number'], 'start_time' => false, 'end_time' => false);

                    if($lastrow == null){
                        // take offline as first entry
                        if($row['number_status'] == 0) {
                            $number[$i]['end_time'] = $row['status_time'];
                        }
                    } else {
                        // if two repeated entry for online/offline skip it
                        if($lastrow['number_status'] == 0 && $row['number_status'] == 0) 
                            continue;
                        if($lastrow['number_status'] == 1 && $row['number_status'] == 1)
                            continue;

                        if($row['number_status'] == 1){
                            $number[$i]['start_time'] = $row['status_time'];
                        }
                        else  {
                            $number[$i]['end_time'] = $row['status_time'];  
                        }
                        if($number[$i]['start_time'] && $number[$i]['end_time'])
                            $i++;

                    }

                    $lastrow = $row;

                }

            }
            $stmt->close();
            return $number;
    }

我得到了上面函数的json响应，如下所示

{"code":1,"error":false,"received":"2018-07-15 00:00:00","created_at":"2018-07-15 00:00:00","end":"2018-07-16 00:00:00","logs":[{"number":"919400000001","start_time":"2018-07-15 16:11:04","end_time":"2018-07-15 16:12:03"},{"number":"919400000001","start_time":"2018-07-15 10:35:47","end_time":"2018-07-15 10:37:34"},{"number":"919400000001","start_time":"2018-07-15 10:31:03","end_time":"2018-07-15 10:33:43"},{"number":"919400000001","start_time":"2018-07-15 10:27:28","end_time":"2018-07-15 10:27:46"},{"number":"919400000001","start_time":"2018-07-15 10:26:55","end_time":"2018-07-15 10:27:26"},{"number":"919400000001","start_time":"2018-07-15 10:25:38","end_time":"2018-07-15 10:25:50"},{"number":"919400000001","start_time":"2018-07-15 10:24:51","end_time":"2018-07-15 10:25:14"},{"number":"919400000000","start_time":"2018-07-15 10:55:18","end_time":"2018-07-15 16:11:04"},{"number":"919400000000","start_time":"2018-07-15 10:33:50","end_time":"2018-07-15 10:34:04"},{"number":"919400000000","start_time":"2018-07-15 10:27:20","end_time":"2018-07-15 10:27:38"},{"number":"919400000000","start_time":"2018-07-15 10:25:42","end_time":"2018-07-15 10:25:57"},{"number":"919400000000","start_time":"2018-07-15 10:24:57","end_time":"2018-07-15 10:25:22"}]}

但我想按结束时间排序（脱机）。我不知道该怎么做。在我的数据库表中，有单独的联机（开始时间）和脱机（结束时间）条目。如果有人能帮我拿，请告诉我。谢谢

下面的内容可能会满足您的需求，但会涉及对php代码的一些更改。

select number, min(start_time), end_time 
from (
  select t1.number as number, 
         t2.status_time as start_time, 
         min(t1.status_time) as end_time 
  from number_status t1 
    inner join number_status t2
      on 
          t1.number=t2.number and 
          t1.number_status=0 and 
          t2.number_status=1 and 
          t1.status_time > t2.status_time and
          t1.user_id=t2.user_id
  where 
     (t1.number=? or t1.number=?) AND 
     t1.user_id=? AND 
     t2.status_time > ? AND t1.status_time < ?
  group by t1.number, t2.status_time ) t
GROUP BY number, end_time
ORDER BY end_time DESC;

查询的工作方式如下：首先，number\u status表的内部联接与自身一起创建一个包含number、start\u time和end\u time的条目列表（t1 label表示end\u time，t2 label表示start\u time）。对于连接条件，这将包括从t2开始的每个可能的开始时间和从t1开始的每个可能的结束时间，这些时间大于开始时间。
where部分限制一组数字、用户id和状态时间值。按（t1.number，t2.status\u time）和select中的min（t1.status\u time）进行分组，只留下那些具有相同开始时间、相同编号和最小可能结束时间的记录。
周围的选择有两个目标：第一，为每个结束时间选择可用的最小开始时间（同样是分组时间和最小值）；其次，按结束时间对结果排序（在本例中，我选择了desc-但是asc也是可能的）。
我确信同样的事情也可以很容易地直接在php中完成，但是我不知道如何。。。

防止表中出现多个条目

1条答案

相关问题

热门标签

最新问答