Mysql Select 一对列存在最短日期的记录列

发布于05月03日

我有一个SQL视图"data"，看起来如下所示:

CALMONTH	COSTCENTER	KONTONUMMER	FIELDNM003
202212	Alpha	123	10
202301	Alpha	123	20
202210	Alpha	223	50
202307	Beta	123	10

我只想保留"COSTCENTER"和"KONTONUMMER"的组合，其中有一个或多个条目具有"CalibMONTH"= 202301.所需的输出是:

CALMONTH	COSTCENTER	KONTONUMMER	FIELDNM003
202212	Alpha	123	10
202301	Alpha	123	20
202307	Beta	123	10

目前我只能在"COSTCENTER"一栏中这样做:

SELECT "t"."CALMONTH", "t"."COSTCENTER", "t"."KONTONUMMER", "t"."FIELDNM003"
FROM "data" AS t
INNER JOIN (
  SELECT "COSTCENTER"
  FROM "data"
  GROUP BY "COSTCENTER"
  HAVING MAX("CALMONTH") > 202301
) AS s ON s.COSTCENTER = t.COSTCENTER

推荐答案

您应该识别任何至少有一个"CalibMONTH"= 202301条目的COSTCENTER和Kontonummer对.找到这些对后，将它们与您的表连接起来以检索所有相应的行:

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'