时间戳

首页 > 解决方案 > 仅获取最近的 MySQL 事务

mysql - 仅获取最近的 MySQL 事务

问题描述

我正在尝试从一个 WordPress 数据库导出一组用户信息以导入另一个数据库。但是，我无法弄清楚如何仅为用户导出最近的会员交易。以下查询导出每个用户的所有交易，但我只想要具有最新“t.expires_at”值的交易。我已经尝试了基于其他 StackOverflow 线程的各种子查询等，但无法完全弄清楚。

SELECT DISTINCT
    u.user_login AS user_login, 
    u.user_email AS user_email, 
    u.user_registered AS user_registered, 
    f.meta_value AS first_name, 
    l.meta_value AS last_name,
    t.expires_at AS membership_enddate
FROM 
    wp_acfzia_users AS u
LEFT JOIN 
    wp_acfzia_usermeta AS f
    ON u.ID = f.user_id AND f.meta_key = 'first_name'
LEFT JOIN 
    wp_acfzia_usermeta AS l
    ON u.ID = l.user_id AND l.meta_key = 'last_name'
LEFT JOIN
    wp_acfzia_mepr_transactions AS t
    ON u.ID = t.user_id

标签： mysqlgreatest-n-per-group

解决方案

试试这个方法

SELECT DISTINCT
    u.user_login AS user_login, 
    u.user_email AS user_email, 
    u.user_registered AS user_registered, 
    f.meta_value AS first_name, 
    l.meta_value AS last_name,
    t.expires_at AS membership_enddate
FROM 
    wp_acfzia_users AS u
LEFT JOIN 
    wp_acfzia_usermeta AS f
    ON u.ID = f.user_id AND f.meta_key = 'first_name'
LEFT JOIN 
    wp_acfzia_usermeta AS l
    ON u.ID = l.user_id AND l.meta_key = 'last_name'
LEFT JOIN
    wp_acfzia_mepr_transactions AS t
    ON u.ID = t.user_id
WHERE exists (
   select 1 from tbl where tbl.user_id = t.user_id 
   having max(tbl.expires_at) = t.expires_at
)

或 MySQL 8+

select * from (
    SELECT DISTINCT
        u.user_login AS user_login, 
        u.user_email AS user_email, 
        u.user_registered AS user_registered, 
        f.meta_value AS first_name, 
        l.meta_value AS last_name,
        t.expires_at AS membership_enddate,
        row_number() over (order by t.expires_at partition by user_id) rn
    FROM 
        wp_acfzia_users AS u
    LEFT JOIN 
        wp_acfzia_usermeta AS f
        ON u.ID = f.user_id AND f.meta_key = 'first_name'
    LEFT JOIN 
        wp_acfzia_usermeta AS l
        ON u.ID = l.user_id AND l.meta_key = 'last_name'
    LEFT JOIN
        wp_acfzia_mepr_transactions AS t
        ON u.ID = t.user_id
)
Where rn = 1

推荐阅读