>我有这样的表格:
我的表名是:user_viewed_offer
id user_id coupon_id
1 1 65
2 1 58
3 1 65
4 1 65
5 1 34
6 1 46
7 1 24
我想检索这些 ID:
4-5-6-7
我用过group by
但它返回这些 id:
"[{"id":"7"},{"id":"6"},{"id":"5"},{"id":"2"}]"
我的职能:
$this->db->select('id');
$this->db->from('user_viewed_offer');
$this->db->where('user_id',$user_id);
$this->db->group_by('coupon_id');
$this->db->order_by('id','desc');
$this->db->limit('4');
$data['coupons'] = $this->db->get()->result();
var_dump(json_encode($data['coupons']));
exit();
我想我使用了distinct
语句,但我不知道我应该如何使用它。
将您的表连接到一个子查询,该子查询查找每个用户、每个优惠券的最新id
值。
SELECT t1.id
FROM yourTable t1
INNER JOIN
(
SELECT user_id, coupon_id, MAX(id) AS max_id
FROM yourTable
GROUP BY user_id, coupon_id
) t2
ON t1.user_id = t2.user_id AND
t1.coupon_id = t2.coupon_id AND
t1.id = t2.max_id
WHERE
t1.user_id = 1;
似乎您想检索从最大值开始的 id coupon_id
select id from user_viewed_offer
where id => (
select max(id)
from user_viewed_offer
where coupon_id = (
select max(coupon_id)
from user_viewed_offer
)
)