gpt4 book ai didi

mysql - 如何对我的 mysql 结果进行分组

转载 作者:行者123 更新时间:2023-11-29 14:31:46 24 4
gpt4 key购买 nike

我在 mysql 中有下表

+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_profile_id | int(11) | NO | MUL | NULL | |
| latitude | float(10,6) | YES | | NULL | |
| longitude | float(10,6) | NO | | NULL | |
| modified | datetime | YES | | NULL | |
| created | datetime | YES | | NULL | |
+-----------------+-------------+------+-----+---------+----------------+

我想做一个查询,传递 user_profile_id 列表并获取他们的最新记录。每个user_profile_id有多条记录

sql : select id,user_profile_id,latitude,longitude,modified,created from user_locations where user_profile_id in(1044,1259);

+----+-----------------+-----------+-------------+---------------------+-------------------- -+
| id | user_profile_id | latitude | longitude | modified | created |
+----+-----------------+-----------+-------------+---------------------+---------------------+
| 14 | 1044 | 49.276867 | -123.120689 | 2011-12-24 00:50:22 | 2011-09-06 19:09:18 |
| 59 | 1044 | 49.276867 | -123.120689 | 2011-08-05 12:12:12 | 2011-11-01 00:00:00 |
| 60 | 1044 | 49.276867 | -123.120689 | 2010-08-05 12:12:12 | 2010-11-01 00:00:00 |
| 61 | 1044 | 49.276867 | -123.120689 | 2009-08-05 12:12:12 | 2009-11-01 00:00:00 |
| 62 | 1044 | 49.276867 | -123.120689 | 2008-08-05 12:12:12 | 2008-11-01 00:00:00 |
| 41 | 1259 | 49.276722 | -123.120735 | 2011-12-08 19:53:39 | 2011-12-07 19:38:02 |
+----+-----------------+-----------+-------------+---------------------+---------------------+

示例:

select *,max(created) from user_locations where user_profile_id IN (1044,1259) group by user_profile_id; 

这个查询是错误的,如何解决这个问题?谢谢

最佳答案

select u.id,u.user_profile_id,u.latitude,u.longitude,u.modified,u.created 
from user_locations u where u.user_profile_id in(1044,1259) and u.created =
(select max(g.created) from user_locations g where u.user_profile_id=g.user_profile_id) group by u.user_profile_id;

关于mysql - 如何对我的 mysql 结果进行分组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9879773/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com