时间戳

首页 > 解决方案 > 如何获取重复 usid 的特定日期范围的最新数据

php - 如何获取重复 usid 的特定日期范围的最新数据

问题描述

我有2 张桌子。一个是用户详细信息，另一个是付款表。我只想在一个月内获得该用户的最新数据。

$sql="SELECT a.usr_id, a.fname, a.lname, c.user_id, c.payment_dt 
       FROM user_details a 
       INNER JOIN payment c 
               ON a.usr_id = c.user_id 
       INNER JOIN (SELECT user_id, 
                          Max(payment_dt) maxDate 
                   FROM   payment 
                   GROUP  BY user_id) b 
               ON c.user_id = b.user_id 
                  AND c.payment_dt = b.maxdate 
WHERE  c.payment_dt BETWEEN '$start_dt' AND '$end_dt' ";
    $ex = $this->db->query($sql);
    $data['info']  = $ex->result();

标签： phpsqlcodeigniter

解决方案

如果您有多个用户 ID，您的查询将产生多行。我认为您正在尝试在给定的用户 ID 上获得结果？添加这可能会有所帮助and a.user_id = '$userID'

$sql="SELECT a.usr_id, a.fname, a.lname, c.user_id, c.payment_dt 
       FROM user_details a 
       INNER JOIN payment c 
               ON a.usr_id = c.user_id 
       INNER JOIN (SELECT user_id, 
                          Max(payment_dt) maxDate 
                   FROM   payment 
                   GROUP  BY user_id) b 
               ON c.user_id = b.user_id 
                  AND c.payment_dt = b.maxdate 
WHERE  c.payment_dt BETWEEN '$start_dt' AND '$end_dt' and a.user_id = '$userID' ";
    $ex = $this->db->query($sql);
    $data['info']  = $ex->result();

推荐阅读