在mysql中获取max的相对数据

I have a table called log_payment that has a series of payment records like:

我有一个名为log_payment的表,其中包含一系列付款记录,例如:

log_user_id, log_date, log_payment_id

13, 2013-01-01 01:13:00, TRIAL<BR>
13, 2013-01-02 01:18:00, 1<BR>
13, 2013-01-03 01:05:00, 2

What I want to get is the payment id and date of the users last record. So I want that user_id's last transaction was 01/03 and has a payment id of 2. So I wrote this query:

我想得到的是用户上次记录的付款ID和日期。所以我希望user_id的最后一笔交易是01/03,支付ID为2.所以我写了这个查询:

select max(log_date) as max_date,log_user_id,log_payment_id from log_payment group by log_user_id

but it returns 13, 2013-01-03 01:05:00, TRIAL

但它返回13,2013-01-03 01:05:00,试用

So based on some data I found somewhere else, I tried this:

所以根据我在其他地方找到的一些数据,我试过这个:

select log_user_id, max_date, log_payment_id from (select log_user_id,max(log_date) as max_date from log_payment group by _log_user_id) payment_table inner join log_payment on payment_table.log_user_id = log_payment.log_user_id and payment_table.max_date = log_payment.log_date

But this goes on for several minutes until I finally just cancel it. What am I missing?

但这持续了几分钟,直到我最终取消它。我错过了什么?

2 个解决方案

#1

Your query, which I have reparsed, looks good, except for the _log_user_id in the group by. It should be log_user_id:

您重新分析的查询看起来很好,除了组中的_log_user_id。它应该是log_user_id:

 select log_user_id, 
        max_date, 
        log_payment_id from 
        (select log_user_id,max(log_date) as max_date from log_payment group by _log_user_id)
              payment_table 
        inner join 
        log_payment 
        on payment_table.log_user_id = log_payment.log_user_id and 
           payment_table.max_date = log_payment.log_date

Depending on the size of your tables the query might be slow. Try adding a LIMIT 10 at the end of the query to see if that gets you the desired result for the first 10 tuples.

根据表的大小,查询可能会很慢。尝试在查询结尾添加一个LIMIT 10,看看是否能获得前10个元组的预期结果。

--dmg

#2

The best solution for the Group by order is use a subquery to make the order by for you:

按订单分组的最佳解决方案是使用子查询为您下达订单:

SELECT t1.*
FROM `log_payment` t1
WHERE `id` = (
    SELECT `id`
    FROM `log_payment` `t2`
    WHERE `t2`.`log_user_id` = `t1`.`log_user_id`
    ORDER BY `t2`.`log_date` DESC
    LIMIT 1
)

It also should be really fast. Of course it always relies on your index's setup.

它也应该非常快。当然,它总是依赖于索引的设置。

#1