在sql中只保留具有最短日期的列组合

您应该识别任何COSTCENTER和Kontonsummer对,它们至少有一个条目,其中“CALMONTH”>=202301.找到这些对后,将它们与表连接以检索所有对应的行:

SELECT t.*
FROM mytable t
inner join (
  select COSTCENTER, Kontonummer
  from mytable
  where CALMONTH >= 202301
  group by COSTCENTER, Kontonummer
) as s on s.COSTCENTER = t.COSTCENTER
       and s.Kontonummer = t.Kontonummer;

Demo here

如果您使用的是mysql 8或更高版本,这是使用窗口函数的另一种方法 MAX() :

select CALMONTH, COSTCENTER, Kontonummer, FIELDNM003
from (
  select *, max(CALMONTH) over (partition by COSTCENTER, Kontonummer) as max_CALMONTH
  from mytable
) as s
where max_CALMONTH >= '202301'

一个简单的exists查询应该做到这一点:

SELECT *
FROM data AS t
WHERE EXISTS (
    SELECT *
    FROM data AS x
    WHERE costcenter = t.costcenter
    AND   kontonummer = t.kontonummer
    AND   calmonth >= 202301
)

你可以使用一个简单的 IN 子句,因为它们在MySQL中处理多个列:

SELECT *
FROM mytable
WHERE
  (costcenter, kontonummer) IN
    (SELECT costcenter, kontonummer
     FROM mytable
     WHERE calmonth >= 202301);

内部查询获取满足日期条件的所有对,外部查询添加其他列。

此处示例: db<>fiddle