我想让每个用户和他们最后一次付款。
我想让每个用户和他们最后一次付款。我有2代表在这里users和finances。我尝试添加groupby，但得到了想要的结果，但是它获得了另一个表中最旧的记录。有谁知道我怎么能做到这一点？
我的第一个查询
SELECT users.name, users.email, users.phone, users.parent_id, users.section_id, finances.amount, finances.description, schoolyears.name, finances.date from users JOIN finances on users.id = finances.user_id JOIN schoolyears on users.school_id = schoolyears.school_id ORDER BY finances.date DESC; 结果我得到了
+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+ | name | email | phone | parent_id | section_id | amount | description | name | date | +-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+ | Madelynn Stokes | moore.dominic@cartwright.com | +63 (971) 659-8143 | 10 | NULL | 1000 | New Payables | SY-2019-2020 | 2019-11-14 | | Annamarie Morar | emile99@hotmail.com | (0997) 212-7919 | 3 | NULL | 500 | New Pays | SY-2019-2020 | 2019-11-14 | | Madelynn Stokes | moore.dominic@cartwright.com | +63 (971) 659-8143 | 10 | NULL | 5000 | Old Payables | SY-2019-2020 | 2019-11-13 | | Annamarie Morar | emile99@hotmail.com | (0997) 212-7919 | 3 | NULL | 200 | Old Pays | SY-2019-2020 | 2019-11-13 | +-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+ 我只想要其他表中的最新记录。新应付款和新工资。
我尝试了第二个查询
SELECT users.name, users.email, users.phone, users.parent_id, users.section_id, finances.amount, finances.description, schoolyears.name, finances.date from users JOIN finances on users.id = finances.user_id JOIN schoolyears on users.school_id = schoolyears.school_id GROUP BY users.id ORDER BY finances.date DESC; 它有效，但是我得到了最早的记录。
+-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+ | name | email | phone | parent_id | section_id | amount | description | name | date | +-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+ | Madelynn Stokes | moore.dominic@cartwright.com | +63 (971) 659-8143 | 10 | NULL | 5000 | Old Payables | SY-2019-2020 | 2019-11-13 | | Annamarie Morar | emile99@hotmail.com | (0997) 212-7919 | 3 | NULL | 200 | Old Pays | SY-2019-2020 | 2019-11-13 | +-----------------+------------------------------+--------------------+-----------+------------+--------+--------------+--------------+------------+
问题来源于stack overflow
写回答
-
您可以在下面尝试-
SELECT users.name, users.email, users.phone, users.parent_id, users.section_id, finances.amount, finances.description, schoolyears.name, finances.dateval from users JOIN finances on users.id = finances.user_id JOIN schoolyears on users.school_id = schoolyears.school_id where finances.dateval= (select max(dateval) from finances f where finances.user_id=f.user_id)
2019-11-14 18:06:32赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
