选择"TOP 1"在发生异常情况时不返回值

Question

这是我的数据链接。

我有这个问题：

SELECT *
FROM tbl c
WHERE C.dep = (select top 1 dep
from tbl cc
where cc.yea = c.yea
and cc.mon = mon
group by mon, yea, dep, n
order by n desc)
OR C.dep =(     select top 1 dep
from tbl cc
where cc.yea = c.yea
and cc.mon = mon
group by mon, yea, dep, n
order by n asc 
)
ORDER BY yea, mon, n

则对于每个CCD_ 1返回最佳(最低n(和最差(最高n(的CCD_。此查询适用于第1,2,4,5,7个月，而不适用于第3,6个月。唯一的区别是，在3和6个案例中，我得到了两个得分相同的del(1(。我怎么能退回其中一个，而不是什么都不退回。

这是我的输出：

n           yea        mon        dep
----------- ----------- ----------- ----------
1           2017        1           50
48          2017        1           36
58          2017        2           36
85          2017        3           36
1           2017        4           50
39          2017        4           36
1           2017        5           50
39          2017        5           36
19          2017        6           36
3           2017        7           50
17          2017        7           36

这就是我所期望的：

n           yea.        mon         dep
----------- ----------- ----------- ----------
1           2017        1           50
48          2017        1           36
58          2017        2           36
85          2017        3           36
1           2017        3           49 (or 67)
1           2017        4           50
39          2017        4           36
1           2017        5           50
39          2017        5           36
1           2017        6           50 (or 13)
19          2017        6           36
3           2017        7           50
17          2017        7           36

Answer 1

使用row_number():

select t.*
from (select t.*,
row_number() over (partition by yea, mon order by n asc) as seqnum_asc,
row_number() over (partition by yea, mon order by n desc) as seqnum_desc
from tbl t
) t
where 1 in (seqnum_asc, seqnum_desc);