mysql - 为数组中的所有 id 选择列上具有最大值的所有行

Question

我有以下带有列的表格标记:

LOC(Blob)
LOCDTL
LOCTIME
CARRIERTYPE
CARRIERID

我想在查询本身的数组中获取来自前端端的每个 id 的 MAX(LOCTIME) 值对应的行。

我已经尝试过这种可能性，但没有一个能按照我想要的方式工作。

SELECT X(LOC), Y(LOC), LOCDTL, LOCTIME, UPPER(CARRIERTYPE), CARRIERID
FROM  LOC_EVENT_CARRIER_REPO
WHERE UPPER(TRIM(CARRIERTYPE)) = "VESSEL"
  AND UPPER(TRIM(CARRIERID)) IN (35, 69,70,71,72,73,74)
  AND LOCTIME IN (SELECT MAX(LOCTIME) FROM LOC_EVENT_CARRIER_REPO
                  WHERE UPPER(CARRIERTYPE) = "VESSEL"
                    AND CARRIERID IN (35, 69,70,71,72,73,74))
group by CARRIERID;

Answer 1

如果我没猜错的话。找到最后一行，在提供的 id 列表中为每个 CARRIERID 指定 MAX(LOCTIME)

SELECT X(LOC), Y(LOC), LOCDTL, LOCTIME, UPPER(CARRIERTYPE), CARRIERID
FROM  LOC_EVENT_CARRIER_REPO e
JOIN (
      SELECT CARRIERID, MAX(LOCTIME) maxlt
      FROM LOC_EVENT_CARRIER_REPO 
      WHERE UPPER(CARRIERTYPE) = "VESSEL"
                    AND CARRIERID IN (35, 69,70,71,72,73,74)
      GROUP BY CARRIERID 
    ) m ON e.CARRIERID = m.CARRIERID AND e.LOCTIME = m.maxlt