sql - 如何修复 “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” 错误？

Question

我的查询如下，其中包含一个子查询:

select a.bill_prvdr_acct_id, a.acct_id, a.bill_prvdr_acct_strt_dt, a.bill_prvdr_acct_end_dt
from xxxx_snapshot.dbo.bill_prvdr_acct_history a
where a.acct_id in 
(select acct_id, count(*)
from xxxx_snapshot.dbo.bill_prvdr_acct_history 
group by acct_id
having count(*) > 1)

我收到错误消息“当子查询未使用 EXISTS 引入时，只能在选择列表中指定一个表达式。”

Answer 1

鉴于您的查询结构，窗口函数可能是一种更简单的方法来执行您想要的操作:

select a.bill_prvdr_acct_id, a.acct_id, a.bill_prvdr_acct_strt_dt, a.bill_prvdr_acct_end_dt
from (select a.*, count(*) over (partition by acct_id) as cnt
      from xxxx_snapshot.dbo.bill_prvdr_acct_history a
     ) a
where cnt > 1;