Two tables MAX DATE

Question

我有两个表:

表A有列account, b, c表B有列account, date

如何加入WHERE TABLEA.account=TABLEB.account

这就是我正在努力的地方，我只是想从TABLE B中获得表B上每个帐户的MAX(日期)行。

Answer 1

甲骨文……
WITH子句在这里只是生成示例数据，它不是答案的一部分。
你没有说如果表B中没有与表a中的ACCT相匹配的记录怎么办?这是一个简单的解决方案，可以为表a中的每个帐户提供表B中的最大日期。

WITH 
a AS
(
SELECT 1 "ACCT", 'This is B_1' "B", 'This is C_1' "C" FROM DUAL UNION ALL
SELECT 2 "ACCT", 'This is B_2' "B", 'This is C_2' "C" FROM DUAL UNION ALL
SELECT 3 "ACCT", 'This is B_3' "B", 'This is C_3' "C" FROM DUAL UNION ALL
SELECT 4 "ACCT", 'This is B_4' "B", 'This is C_4' "C" FROM DUAL 
),
b AS
(
SELECT 1 "ACCT", '2022-02-24' "DATE_COL" FROM DUAL UNION ALL
SELECT 2 "ACCT", '2022-01-01' "DATE_COL" FROM DUAL UNION ALL
SELECT 3 "ACCT", '2022-02-21' "DATE_COL" FROM DUAL UNION ALL
SELECT 4 "ACCT", '2022-02-28' "DATE_COL" FROM DUAL UNION ALL
SELECT 1 "ACCT", '2022-03-24' "DATE_COL" FROM DUAL UNION ALL
SELECT 1 "ACCT", '2022-04-15' "DATE_COL" FROM DUAL UNION ALL
SELECT 2 "ACCT", '2022-05-12' "DATE_COL" FROM DUAL UNION ALL            
SELECT 3 "ACCT", '2022-02-01' "DATE_COL" FROM DUAL UNION ALL
SELECT 1 "ACCT", '2022-01-31' "DATE_COL" FROM DUAL UNION ALL
SELECT 4 "ACCT", '2022-07-01' "DATE_COL" FROM DUAL UNION ALL
SELECT 4 "ACCT", '2022-06-02' "DATE_COL" FROM DUAL 
)
SELECT a.ACCT "ACCT", Max(b.DATE_COL) "DATE_COL"
FROM a
INNER JOIN b ON(b.ACCT = a.ACCT)
GROUP BY a.ACCT
ORDER BY a.ACCT
--
--  R e s u l t
--
--        ACCT DATE_COL 
--  ---------- ----------
--           1 2022-04-15 
--           2 2022-05-12 
--           3 2022-02-21 
--           4 2022-07-01