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;

标签： mysqlsqlaggregate-functionsgreatest-n-per-group

Answer 1

如果我做对了。MAX(LOCTIME)在提供的CARRIERIDid 列表中找到最后一行

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