需要获取每个用户最近购买的积分的剩余积分，仅适用于购买积分的用户 <= 100

Question

只有当used_at和expired_at为null时，信用才算作剩余信用

用户表：

id	电子邮件
1	行
2	行

而不是联接表"user_credit_history"；您可以通过在字段"的子选择中使用order by，使用子选择来获得每个用户想要的1行；used_at"；。

1. you can use :-
SELECT 
u.id,
u.email,
u.name,
uc.id AS credit_id,
uc.added_at AS purchase_date,
uc.credits_added,
COUNT(uch.credit_id) AS credits_remaining
FROM
user u
LEFT JOIN
(SELECT 
user_id, id, added_at, credits_added
FROM
user_credit
GROUP BY user_id
HAVING MAX(purchase_date)) AS uc ON uc.user_id = u.id
LEFT JOIN
user_credit_history uch ON uch.credit_id = uc.id
WHERE
uc.type = 'CREDIT'
AND uc.credits_added >= 100
AND (uch.used_at IS NULL
AND uch.expired_at IS NULL)
GROUP BY uch.credit_id
ORDER BY uc.added_at DESC;

这很难理解，因为没有预期的结果。。。这个查询为每个用户计算其最近购买的商品，并获取它，并计算该用户最近购买的credit_id的剩余信用，我添加了100过滤器：

select u.id, u.email, u.name, uc.id as credit_id, uc.added_at as purchase_date, uc.credits_added, count(credit_id) as credits_remaining
from
(
select user_id, max(added_at) as latest_purchase
from user_credit
group by user_id
) up
inner join user_credit uc on uc.user_id=up.user_id and uc.added_at=up.latest_purchase
inner join users u on u.id=uc.user_id
inner join user_credit_history uch on uch.credit_id=uc.id
where uc.type='CREDIT' and 
uc.credits_added >= 100 and 
used_at is null and expired_at is null
group by uch.credit_id, u.id, u.email, u.name, uc.id as credit_id, uc.added_at as purchase_date, uc.credits_added