如何获取每个用户的最新行记录 调整现有SQL实现全用户最新数据展示
解决方案
方案1:窗口函数实现(适配MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库)
核心逻辑是按login_id分组,组内按company_id倒序排序取第一条,就是每个用户的最新记录,性能和可读性都更好:
WITH latest_company AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY login_id ORDER BY company_id DESC) AS rn FROM company21 -- 过滤条件和原有逻辑保持一致 WHERE is_active = 1 AND is_saved = 1 AND send_for_approval = 1 ) SELECT * FROM latest_company as c INNER JOIN industryList as ind on c.industry = ind.industry_id LEFT JOIN company21_badge_list as bd on c.badge_logo = bd.bd_id LEFT JOIN company21_login as l on c.login_id = l.login_id WHERE c.rn = 1;
提示:如果同一用户存在多条相同最大
company_id的记录需要全部返回,把ROW_NUMBER替换为RANK即可。
方案2:兼容低版本MySQL实现(基于你原有SQL修改)
适合还在使用MySQL 5.x不支持窗口函数的场景,改动点很小:
SELECT * FROM company21 as c INNER JOIN industryList as ind on c.industry = ind.industry_id LEFT JOIN company21_badge_list as bd on c.badge_logo = bd.bd_id LEFT JOIN company21_login as l on c.login_id = l.login_id WHERE c.company_id IN ( SELECT MAX(company_id) AS c_id FROM company21 WHERE is_active = 1 AND is_saved = 1 AND send_for_approval = 1 GROUP BY login_id )
主要改动说明:
- 删掉子查询里固定的
login_id = 4限制,按login_id分组就能拿到所有用户各自的最大company_id - 把原来的
c.company_id =改为c.company_id IN,匹配所有用户的最大company_id - 去掉了原
GROUP BY后多余的DESC排序,分组操作不需要加排序参数
内容的提问来源于stack exchange,提问作者user9437856
相关产品推荐
相关产品推荐

