仅显示MAX行

Question

我有一个表，它显示了邮局的工作时间，问题是有时会有重复：例如，我在同一时间从一个邮局显示两次星期六。解决方案是只显示1个星期六的MAX(ID(，但我无法处理这一问题，我认为我不必在select中显示ID。这是我的脚本

SELECT h.ID,
h.POSTINDEX,
H.LONGNAME_UA,
h.SHORTNAME_UA,
pt.LONGNAME_UA,
h.parent_Id,
WORKCOMMENT,
INTERVALTYPE,
TO_CHAR(TFROM, 'HH24:MI') AS TFROM,
TO_CHAR(TTO, 'HH24:MI'),
WD.NAME_UA,
WD.NAME_EN,
WD.NAME_RU,
WD.SHORTNAME_UA,
pt.isVPZ,
lr.NAME_UA,
lr.CODE
FROM ADDR_PO_WORKSCHEDULE tt
LEFT JOIN ADDR_POSTOFFICE h
ON tt.POSTOFFICE_ID = h.ID
INNER JOIN mdm_lockReason lr
ON lr.id = H.LOCK_REASON
INNER JOIN ADDR_POSTOFFICEtype pt
ON pt.ID = H.POSTOFFICETYPE_ID
INNER JOIN ADDR_PO_WORKDAYS wd
ON wd.ID = tt.dayofweek
where tt.datestop = TO_DATE('9999-12-31','YYYY-MM-DD') AND tt.postoffice_id = 8221
HAVING MAX(tt.ID)
ORDER BY h.postIndex,
h.POSTOFFICETYPE_ID,
dayofweek,
intervaltype,
tFrom,
tto

正如你在那里看到的，我添加了HAVING MAX(tt.ID(，但我知道这是不正确的，不知道如何解决。请帮忙！

Answer 1

您可以使用row_number():

with t as (
<your query here with no `having` and with tt.id as tt_id>
)
select t.*
from (select t.*, row_number() over (order by tt_id desc) as seqnum
from t
) t
where seqnum = 1;

Answer 2

如果重复行的来源来自ADDR_PO_WORKSCHEDULE表，那么您可以在将其用于查询的其余部分之前对其进行重复数据消除：

SELECT 
h.ID, h.POSTINDEX, h.LONGNAME_UA, h.SHORTNAME_UA, pt.LONGNAME_UA, h.parent_Id,
WORKCOMMENT, INTERVALTYPE, TO_CHAR(TFROM, 'HH24:MI') AS TFROM, TO_CHAR(TTO, 'HH24:MI'),
WD.NAME_UA, WD.NAME_EN, WD.NAME_RU, WD.SHORTNAME_UA, pt.isVPZ, lr.NAME_UA, lr.CODE
FROM (
SELECT src.*
FROM (
SELECT t.*, 
ROW_NUMBER() OVER(
PARTITION BY tt.postoffice_id, tt.dayofweek -- Group by post office / day of week
ORDER BY <order_columns> -- Rank rows within each group
) AS RowNum
FROM ADDR_PO_WORKSCHEDULE t
) src
WHERE RowNum = 1
) tt
LEFT JOIN ADDR_POSTOFFICE h ON tt.POSTOFFICE_ID = h.ID
INNER JOIN mdm_lockReason lr ON lr.id = H.LOCK_REASON
INNER JOIN ADDR_POSTOFFICEtype pt ON pt.ID = H.POSTOFFICETYPE_ID
INNER JOIN ADDR_PO_WORKDAYS wd ON wd.ID = tt.dayofweek
WHERE tt.datestop = TO_DATE('9999-12-31','YYYY-MM-DD') 
AND tt.postoffice_id = 8221
ORDER BY h.postIndex, h.POSTOFFICETYPE_ID, dayofweek, intervaltype, tFrom, tto

只需替换为要用来决定保留哪些"重复"行的列。如果顺序无关紧要，只需完全删除ORDER BY即可。

此外，您可能需要限定所有列引用，因为您是从多个表中进行选择的。

Answer 3

您可以使用以下分析函数：

Select * from
(Select <your select column list>,
Row_number() over (partition by h.postindex, trunc(tfrom) order by tt.id desc nulls last) as rn
From <from clause>
Where <where clause>
)
Where rn = 1;

不需要group by。