如何在mysql中按产品id和最新创建日期分组?

ia2d9nvy  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(305)

我有如下select语句和示例输出:-

select uph.creation_date, p.name,p.product_id from product p 
                left join user_product_history uph on p.product_id = uph.product_id 
                where uph.user_id = 124 order by uph.creation_date desc


如何按产品id和最新创建日期分组?请帮忙。谢谢您。
用php api.model文件编辑

// ~/user/product_history
    public function product_history($data) {
        $sql = 'select uph.creation_date,
        p.name,
        p.product_id
        from product p
        join user_product_history uph
        on p.product_id = uph.product_id and
        uph.user_id = ?
        join (select product_id,
              MAX(creation_date) AS max_creation_date
              from user_product_history
              where user_id = ?
              group by product_id) dt
        on dt.product_id = uph.product_id and
        dt.max_creation_date = uph.creation_date
        order by uph.creation_date desc';

        $result = $this->db->query($sql, array($data['user_id']));
        $records = array();
        foreach( $result->result_array() as $r ) {
            $r['product_id'] = (int) $r['product_id'];
            $r['sub_category_id'] = (int) $r['sub_category_id'];
            $r['merchant_id'] = (int) $r['merchant_id'];
            if (!isset($r['price_discount'])) $r['price_discount'] = '';
            $records[] = $r;
        }
        return $records;
    }
nqwrtyyt

nqwrtyyt1#

首先,你不需要 Left Join 在这里,当你过滤的时候 user_product_history 还有table。似乎您只想显示那些具有 creation_date 对应 user_id = 124 . 所以,你可以简单地使用 Inner Join 相反。
在派生表(子选择查询)中,确定 creation_dateproduct_id .
现在,使用这个结果集连接到主表,在 product_id 以及 creation_date ,以获取完整的行。
请尝试以下操作:

select uph.creation_date, 
       p.name,
       p.product_id 
from product p 
join user_product_history uph 
  on p.product_id = uph.product_id and 
     uph.user_id = 124 
join (select product_id, 
             MAX(creation_date) AS max_creation_date
      from user_product_history 
      where user_id = 124 
      group by product_id) dt 
  on dt.product_id = uph.product_id and 
     dt.max_creation_date = uph.creation_date 
order by uph.creation_date desc
mklgxw1f

mklgxw1f2#

select 
    uph.creation_date, 
    p.name,p.product_id 
from 
    product p 
    left join user_product_history uph on p.product_id = uph.product_id 
where 
    uph.user_id = 124 and
    uph.creation_date = (select
                             max(creation_date)
                         from 
                             user_product_history) 
order by 
    uph.creation_date desc

相关问题