mysql 如果最新ID符合条件,则仅选择最新ID

izkcnapc  于 2023-02-11  发布在  Mysql
关注(0)|答案(2)|浏览(134)

我有一个MySQL表people,如下所示:
| 身份证|个人标识|会议时间|
| - ------|- ------|- ------|
| 1个|二十一|小行星123456|
| 第二章|二十一|小行星123457|
| 三个|二十一|小行星123456|
| 四个|二十二|小行星123470|
| 五个|二十一|无|
| 六个|二十一|小行星123472|
我需要为每个person_id获取一行,但前提是meeting_time介于123451和123460之间,否则就不需要该行。
我也不需要一个更低的id和更高的meeting_time(像id 2),也不需要id 5。但是,目前我得到的是id 2,而不是id 3。
下面是我的疑问:

SELECT MAX(`id`) AS `id`, `person_id`, `meeting_time` 
FROM `people` 
WHERE `meeting_time` > 123451 AND `meeting_time` < 123460 
ORDER BY `id` DESC

编辑:SelVazi给出的答案是有效的,然而,到目前为止:由于存在id 6(我使用此条目扩展了表),它的meeting_time大于最大范围,因此我不应获得任何条目,但我获得了id 3(在我想起我也需要它之前,这是正确的)。
编辑2:MySQL服务器5.5

vtwuwzda

vtwuwzda1#

您可以使用相关子查询。它假定id是唯一的:

select *
from t
where id = (
    select max(id)
    from t as x
    where x.person_id = t.person_id
    and meeting_time between 123451 and 123460
);
lymgl2op

lymgl2op2#

您可以使用inner join执行此操作:
这是为了查找按person_id分组的最大id,然后检查meeting_time是否为between 123451 and 123460

with cte as ( 
  select p.*
  from people p
  inner join (
    SELECT person_id, max(id) as id
    FROM people
    where meeting_time between 123451 and 123460
    group by person_id
  ) as s on s.id = p.id
)
select c.*
from cte c
inner join (
  SELECT person_id, MAX(meeting_time) as max_meeting_time
  FROM people
  group by person_id
) as s on s.person_id = c.person_id and s.max_meeting_time between 123451 and 123460

Demo here
这是mysql 5.5的工作查询

select c.*
from ( 
  select p.*
  from people p
  inner join (
    SELECT person_id, max(id) as id
    FROM people
    where meeting_time between 123451 and 123460
    group by person_id
  ) as s on s.id = p.id
) as c
inner join (
  SELECT person_id, MAX(meeting_time) as max_meeting_time
  FROM people
  group by person_id
) as s on s.person_id = c.person_id and s.max_meeting_time between 123451 and 123460

Demo here

相关问题