SELECT b.*
FROM vip_report_vip_income b
INNER JOIN (SELECT *
FROM (SELECT a.*
FROM vip_report_vip_income a
WHERE 1 = 1
AND a.game_id = 1005
AND YEARWEEK(a.`report_date`, 1) >= YEARWEEK('2018-01-27', 1)
AND YEARWEEK(a.`report_date`, 1) <= YEARWEEK('2018-02-26', 1)
ORDER BY a.create_time DESC
) vb
GROUP BY vip_account_id
) v ON v.id = b.id
思路:先用2个子句查出最新登录的所有用户记录,再联表对账号排重
参考:https://stackoverflow.com/questions/6572110/order-by-date-and-time-before-group-by-name-in-mysql/6572292
执行计划:
SELECT b.*
FROM vip_report_vip_income b
INNER JOIN (SELECT a.*, MAX(a.create_time) AS latest
FROM vip_report_vip_income a
WHERE 1 = 1
AND a.game_id = 1005
AND YEARWEEK(a.`report_date`, 1) >= YEARWEEK('2018-01-27', 1)
AND YEARWEEK(a.`report_date`, 1) <= YEARWEEK('2018-02-26', 1)
GROUP BY vip_account_id
) v ON v.vip_account_id = b.vip_account_id
AND b.create_time = v.latest;
思路:先用1个子句查出最新登录的所有用户记录(并排重),另外附加一个max(登录时间),再联表,根据账号和登录时间筛查排重
参考:https://stackoverflow.com/questions/10030787/using-order-by-and-group-by-together
执行计划: