我想为每个id和每个日期返回一行，这是当天的最大值/最后值-特定日期的最大值或最后值由其时间给定。这就是我到目前为止的收入
select distinct a.AU_ID, MAX([DATE] + ' ' + [TIME]) as 'TIMESTAMPfull'
from TABLE_B b join TABLE_A a on a.AU_ID = b.AU_ID
where a.col like 'A%' and [DATE] BETWEEN 20210408 AND 20220408 and a.AU_ID = 'xxXXxx00001'
group by a.AU_ID, b.col1, b.col2, b.col3
返回给我这个，正如你在20220111中看到的，它返回了5行，但我只需要最后一个发生在20220111:
| AU_ID | TIMESTAMPfull |
|---|---|
| xxXXxx00001 | '20211104 061847 |
| xxXXxx00001 | '20220111 200012 |
| xxXXxx00001 | '20220111 200248 |
| xxXXxx00001 | '20220111 200902 |
| xxXXxx00001 | '20220111 201116 |
| xxXXxx00001 | '20220111 201317 |
| xxXXxx00001 | '20220205 090758 |
如果我理解正确，您可以尝试在子查询中使用ROW_NUMBER窗口函数。
SELECT AU_ID,
TIMESTAMPfull
FROM (
SELECT a.AU_ID,
[DATE] + ' ' + [TIME] TIMESTAMPfull,
ROW_NUMBER() OVER(PARTITION BY [DATE], ORDER BY [TIME] DESC) rn
FROM TABLE_B b
JOIN TABLE_A a on a.AU_ID = b.AU_ID
WHERE a.col like 'A%'
AND [DATE] BETWEEN 20210408 AND 20220408
AND a.AU_ID = 'xxXXxx00001'
) t1
WHERE rn = 1