sql - 仅选择具有相同值的记录

Question

我正在处理将成为视图一部分的 SQL 语句。我需要的是只提取两次具有相同唯一键的记录。查询现在如下所示。

select distinct 
    rscmaster_no_in, rsc_no_in, calendar_year, calendar_month, 
    Wstat_Abrv_Ch,
    h.Wstat_no_in, Staffing_Calendar_Date, payhours,
    l.OTStatus
from 
    vw_all_ts_hire h
left join 
    MCFRS_OTStatus_Lookup l on l.wstat_no_in = h.Wstat_no_in
where 
    rscmaster_no_in in (select rscmaster_no_in from vw_rsc_ECC_splty) 
    and Wstat_Abrv_Ch <> ''
    and h.Wstat_no_in in (103, 107)
    and l.OTStatus in ('ECCOTRemove', 'ECCOTSignup')
    and Staffing_Calendar_Date = '2020-11-01' -- only for the testing purposes. Will be removed later.
order by 
    RscMaster_no_in

我从上面的查询中得到的结果是：

我需要修改 SQL 语句，使最终结果如下所示：

如何修改上述语句以吐出这样的最终结果？

标签： sqlsql-servertsqlcount

Answer 1

使用解析count(*) over ()函数。

with cte as (
  select
      count(*) over (partition by YourUniqueKey) as MyRowCount
  {rest of your query}
)
select *
from cte
where MyRowCount = 2;