我已经尝试了几种不同的方法,但尚未成功。
我试图加入tbl1
和tbl2
,以显示tbl1
中的状态和来自tbl2
的状态,其中tbl1.wonum = tbl2.wonum
和带有max(change_date)
的记录。
有人能帮忙吗?
TBL1
的内容:
WONUM | STATUS
---------------
1 | text
2 | text
3 | text
4 | text
TBL2
的内容:
WONUM | STATUS | CHANGEDATE
-----------------------------
1 | text1 | 28-Oct
1 | text2 | 25-Oct
1 | text3 | 31-Oct
1 | text4 | 1-Oct
2 | text3 | 28-Oct
2 | text3 | 25-Oct
2 | text3 | 31-Oct
3 | text3 | 1-Oct
3 | text3 | 1-Oct
4 | text3 | 25-Oct
4 | text3 | 1-Oct
SqlFiddleDemo
SELECT
*
FROM (
SELECT
T1."WONUM", T1."STATUS", T2."STATUS", T2."CHANGEDATE",
ROW_NUMBER() OVER (PARTITION BY t1."WONUM" ORDER BY "CHANGEDATE" desc) as rn
FROM TBL1 T1
JOIN TBL2 T2
on T1."WONUM" = T2."WONUM"
) t
WHERE t.rn = 1
输出
| WONUM | STATUS_T1 | STATUS_T2 | CHANGEDATE | RN |
|-------|-----------|-----------|---------------------------|----|
| 1 | textABC | text3 | October, 31 2001 00:00:00 | 1 |
| 2 | textDEF | text3 | October, 31 2001 00:00:00 | 1 |
| 3 | textGHI | text3 | October, 01 2001 00:00:00 | 1 |
| 4 | textJKL | text3 | October, 31 2001 00:00:00 | 1 |