SQL 如何修复“当子查询未引入 EXISTS 时，选择列表中只能指定一个表达式”错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27969874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fix “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error?
提问by Ron
My query is as follows, and contains a subquery within it:
我的查询如下，其中包含一个子查询：
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)
and i get the error message "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
并且我收到错误消息“当子查询未通过 EXISTS 引入时，只能在选择列表中指定一个表达式。”
回答by Ron
Count(*)is not necessary in the SELECTclause of the subquery, only in the HAVINGclause - the following should work:
Count(*)SELECT在子查询的子句中不需要，只在HAVING子句中 - 以下应该工作：
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
from xxxx_snapshot.dbo.bill_prvdr_acct_history
group by acct_id
having count(*) > 1)
回答by P?????
Error is due to multiple columns selection in subquery.
错误是由于在子查询中选择了多列。
You cannot select more than one column in subquerywhen you use INoperator in whereclause.
subquery使用IN运算符 inwhere子句时，不能选择多于一列。
As mentioned in error use EXISTSor remove the count(*)from subquerywhich is not needed
为错误使用提到EXISTS或删除count(*)从subquery其中不需要
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 EXISTS (SELECT 1
FROM xxxx_snapshot.dbo.bill_prvdr_acct_history b
WHERE a.acct_id = B.acct_id
GROUP BY acct_id
HAVING Count(*) > 1)
Or do a Inner Join
或者做一个 Inner Join
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
INNER JOIN (SELECT acct_id
FROM xxxx_snapshot.dbo.bill_prvdr_acct_history b
GROUP BY acct_id
HAVING Count(*) > 1) B
ON a.acct_id = B.acct_id
回答by Gordon Linoff
Given the structure of your query, window functions are probably an easier method to do what you want:
鉴于您的查询结构，窗口函数可能是一种更简单的方法来执行您想要的操作：
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;
