如果您只需要每月最后一天的数据，那么您可以使用 LAST_DAY 以及 TRUNC 函数在中的日期 WHERE 条款如下：

SELECT
    CODE,
    DATE AS "DATE", -- removed MAX
    VAL_A
FROM
    TABLE_A
WHERE
    DATE BETWEEN '20090601' AND '20090630'
    AND TRUNC(LAST_DAY(MAX(DATE))) = TRUNC(DATE); -- added this condition
    -- removed the GROUP BY clause

干杯！！

您可以尝试以下代码。在子查询中，您将获得最大日期和代码。这个 WHERE IN 子句用作数据的筛选器。

SELECT code, val_a, date
FROM table_a
WHERE (code, date) IN 
  (SELECT code, MAX(date) 
  FROM table_a
  GROUP BY code)

更一般地说，您可以使用正确的子查询：

select a.*
from table_a a
where a.date = (select max(a1.date) from table_a a1 where a1.code = a.code);

使用行号：

with your_data as (
    select stack(6, 
'00001',500 ,'20191101',
'00001',1000,'20191130',
'00002',200 ,'20191101',
'00002',400 ,'20191130',
'00003',200 ,'20191101',
'00003',600 ,'20191130' ) as (code,val_a,date)
)

select  code,val_a,date
  from
(  
 select code,val_a,date, 
        --partition by code and months, max date first
        row_number() over(partition by code, substr(date, 1,6) order by date desc) rn        
   from your_data d 
)s where rn=1   
;

结果：

OK
code    val_a   date
00001   1000    20191130
00002   400     20191130
00003   600     20191130
Time taken: 54.641 seconds, Fetched: 3 row(s)

我们可以试着用 ROW_NUMBER 解决方案：

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY code ORDER BY date DESC) rn
    FROM table_a
    -- WHERE date BETWEEN '20090601' AND '20090630'
    -- your current WHERE clause is dubious
)

SELECT code, date, val_a
FROM cte
WHERE rn = 1;

请注意，将日期存储为文本不是最佳做法。也就是说，假设您是以固定宽度的iso格式存储日期，在这种情况下，我们仍然可以使用这些日期。还有，你现在 WHERE 子句没有意义，所以我把它注解掉了。