两个不在select query.it中工作的计数函数给出了以下错误，因为“当子查询没有用EXISTS引入时，可以在选择列表中指定一个表达式”。 [英] Two count funcions not working in select query.it gives below error as "one expression can be specified in the select list when the subquery is not introduced with EXISTS"

查看：90
本文介绍了两个不在select query.it中工作的计数函数给出了以下错误，因为“当子查询没有用EXISTS引入时，可以在选择列表中指定一个表达式”。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

无法计算两个选择查询的差异，现在sql查询正在工作

单一计数，在查询中添加额外计数功能后显示错误。如果找到其他任何方案的差异2个查询，请建议。



我尝试过：



SELECT（



SELECT --Rec.gems_preservationmediaexpirationdate，Rec.gems_name，TE.gems_name

--COUNT（DISTINCT TE.gems_tissuedetailid） AS [总组织]，

COUNT（DATENAME（当月，Ref.gems_deathnotificationdatetime）='April'，然后TE.gems_tissuedetailid结束时的不同情况）AS'APR Total Tissues'



来自Filteredgems_Referral AS Ref INNER JOIN

FilteredBusinessUnit AS BU ON Ref.owningbusinessunit = BU.businessunitid INNER JOIN

Filteredgems_recovery AS Rec ON Ref。 gems_referralid = Rec.gems_originatingrefferalid INNER JOIN

Filteredgems_MedicalReview AS MR ON Rec.gems_recoveryid = MR.gems_originatingrecoveryid INNER JOIN

Filteredgems_TissueDetail AS TE ON MR.gems_medicalreviewid = TE.gems_medicalreviewidid



WHERE（ Ref.gems_deathnotificationdatetime IS NOT NULL）

AND（Rec.gems_recoveryintent IN（'1'））

AND（DATENAME（year，Ref.gems_deathnotificationdatetime）='2017'）

和TE.statecode（'0'）

） - （



SELECT - Rec。 gems_preservationmediaexpirationdate，Rec.gems_name

--COUNT（DISTINCT TE.gems_tissuedetailid）AS [总组织]，

COUNT（DATENAME时的DISTINCT案例（月，Ref.gems_deathnotificationdatetime）= '四月'那么TE.gems_tissuedetailid END）AS'APR Total Tissues'

来自Filteredgems_Referral AS参考内部加入

FilteredBusinessUnit AS BU ON Ref.owningbusinessuni t = BU.businessunitid INNER JOIN

Filteredgems_recovery AS Rec On Ref.gems_referralid = Rec.gems_originatingrefferalid INNER JOIN

Filteredgems_MedicalReview AS MR ON Rec.gems_recoveryid = MR.gems_originatingrecoveryid INNER JOIN

Filteredgems_TissueDetail AS TE ON MR.gems_medicalreviewid = TE.gems_medicalreviewidid



WHERE（Ref.gems_deathnotificationdatetime IS NOT NULL）

AND（Rec.gems_recoveryintent IN（'1'））

AND（DATENAME（年，Ref.gems_deathnotificationdatetime）='2017'）

AND Rec.gems_preservationmediaexpirationdate> GETDATE（）



）as'Expired Tissue'

解决方案

好的我发现很简单示例来证明你的查询出了什么问题。



想象一下这样一个简单的表格：

  CREATE   TABLE  [dbo]。[Orders]（
 [OrderID] [ int ]  IDENTITY （ 1 ， 1 ） NOT   NULL ，
 [CustomerID] [< span class =code-keyword> nchar ]（ 5 ） NULL ，
 [OrderDate] [ date ]  NULL  
） ON  [ PRIMARY ] 
 
  INSERT   INTO  ORDERS  VALUES （' < span class =code-string> VINET'，'  1996-Jul-4'）
  INSERT   INTO  ORDERS  VALUES （'  VINET'，'  1996年8月6日）
  INSERT   INTO  ORDERS  VALUES （'  VINET'，'  1996-Sep-2'）
  INSERT   INTO  ORDERS  VALUES （'  VINET'，'  1997-Nov-11'）
  INSERT   INTO  ORDERS  VALUES （< span class =code-string>'  VINET'，'  1997-Nov-12'

现在想象一下我想在这样的计数之间得到一些区别：

  SELECT  
（
  SELECT  COUNT（ distinct  OrderID） AS  A，COUNT（ distinct  CustomerID） AS  B 
  FROM  ORDERS 
） - （
  SELECT  count（ distinct  OrderID） AS  A，COUNT（ distinct  CustomerID） AS  B 
  FROM  ORDERS  WHERE  DATEPART（YEAR，OrderDate）=  1996  
）

我得到的错误与

引用：

当子查询未引入EXISTS时，只能在选择列表中指定一个表达式。

但如果我只统计它有效的一个项目：

  SELECT  
（
  SELECT  count（ distinct  OrderID） AS  A 
 < span class =code-keyword> FROM  ORDERS 
） - （
  SELECT  count（不同 OrderID） AS  A 
  FROM  ORDERS  WHERE  DATEPART（年，OrderDate）=  1996  
）

尽管错误信息显示我们仍然可以获得我们想要的值通过使用公用表表达式，或以微妙的不同方式使用子查询。

我将为每个子查询添加一个额外的列：

  SELECT  COUNT（ distinct  OrderID） AS  A，COUNT（ distinct  CustomerID） AS  B ， 1   AS  C  
  FROM  ORDERS

和

 SELECT count（distinct OrderID）AS A，COUNT（不同的CustomerID）AS B ，1 AS C  
来自ORDERS在哪里DATEPART（年，OrderDate）= 1996

我现在可以加入来自该额外列的每个子查询的结果，例如

;  WITH  C1  AS  
（
 < span class =code-keyword> SELECT  COUNT（ distinct  OrderID） AS  A， COUNT（ distinct  CustomerID） AS  B， 1   AS  C 
  FROM  ORDERS 
），C2  AS  
（
  SELECT  count（ distinct  OrderID） AS  A，COUNT（ distinct  CustomerID） AS  B， 1   AS  C 
  FROM  ORDERS  WHERE  DATEPART（YEAR，OrderDate）=  1996  
）
  SELECT  C1.A-C2.A，C1.B-C2.B 
  FROM  C1 
  INNER   JOIN  C2  ON  C1.C = C2.C

或者如果您更喜欢对CTE的子查询

  SELECT  C1.A-C2.A，C1.B-C2.B 
  FROM （
  SELECT  COUNT（ distinct  OrderID） AS  A，COUNT（< span class =code-keyword> distinct  CustomerID） AS  B， 1   AS  C 
  FROM  ORDERS 
） AS  C1 
  INNER   JOIN （
  SELECT  count（ distinct  OrderID） AS  A，COUNT（ distinct  CustomerID） AS  B， 1   AS  C 
  FROM  ORDERS  WHERE  DATEPART（YEAR，OrderDate）=  1996  
） AS  C2  ON  C1.C = C2.C

所以你的查询看起来像这样：

  SELECT  C1。[APR Total组织]  -  C2。[APR总组织]，C1。[另一个计数]  -  C2。[另一个计数] 
  FROM （
 
  SELECT   1   AS  JoinColumn，
 COUNT（你想要的 else  到 count） AS  [另一个计数]，
 COUNT（ DISTINCT   CASE   WHEN  DATENAME（月，Ref.gems_deathnotificationdatetime）= '  April' 那么 TE.gems_tissuedetailid  END  ） AS  '  APR Total Tissues' 
 
  FROM  Filteredgems_Referral  AS 参考 INNER   JOIN  
 FilteredBusinessUnit  AS  BU  ON  Ref.owningbusinessunit = BU.businessunitid  INNER   JOIN  
 Filteredgems_recovery  AS  Rec  ON  Ref.gems_referralid = Rec.gems_originatingrefferalid  INNER   JOIN  
 Filteredgems_MedicalReview  AS  MR  ON  Rec.gems_recoveryid = MR.gems_originatingrecoveryid  INNER   JOIN  
 Filteredgems_TissueDetail  AS  TE  ON  MR.gems_medicalreviewid = TE.gems_medicalreviewidid 
 
  WHERE （Ref.gems_deathnotificationdatetime  IS   NOT   NULL ）
  AND （Rec.gems_recoveryintent  IN （'  1'））
  AND （DATENAME（year，Ref.gems_deathnotificationdatetime）= '  2017'）
 和 TE.statecode  in （'  0'）
） AS  C1 
  INNER   JOIN （
 
  SELECT   1   AS  JoinColumn，
 COUNT（什么是 else 您希望  count） AS  [另一个计数] ，
 COUNT（ DISTINCT   CASE   WHEN  DATENAME（month，Ref.gems_deathnotificationdatetime）= '  April'  THEN  TE.gems_tissuedetailid  END ）< span class =code-keyword> AS  '  APR Total Tissues' 
  FROM  Filteredgems_Referral  AS 参考 INNER   JOIN  
 FilteredBusinessUnit  AS  BU  ON 参考.owningbusinessunit = BU.businessunitid  INNER   JOIN  
 Filteredgems_recovery  AS  Rec  ON  Ref.gems_referralid = Rec.gems_originatingrefferalid  INNER   JOIN  
 Filteredgems_MedicalReview  AS  MR  ON  Rec.gems_recoveryid = MR.gems_originatingrecoveryid  INNER   JOIN  
 Filteredgems_TissueDetail  AS  TE  ON  MR.gems_medicalreviewid = TE.gems_medicalreviewidid 
 
  WHERE （Ref.gems_deathnotificationdatetime  IS   NOT   NULL ）
  AND （Rec.gems_recoveryintent  IN （'  1'））
  AND （DATENAME（year，Ref.gems_deathnotificationdatetime）= '  2017'）
  AND  Rec.gems_preservationmediaexpirationdate> GETDATE（）
 
） as  C2  ON  C1.JoinColumn = C2.JoinColumn


Not able to count difference of two select queries,Now sql query is working for
"single count only",shows error after adding extra count function in query.If any other scenario to find difference of 2 query,please suggest.

What I have tried:

SELECT (

SELECT --Rec.gems_preservationmediaexpirationdate,Rec.gems_name,TE.gems_name
--COUNT(DISTINCT TE.gems_tissuedetailid) AS [Total Tissues],
COUNT(DISTINCT CASE WHEN DATENAME(month, Ref.gems_deathnotificationdatetime) = 'April' THEN TE.gems_tissuedetailid END) AS 'APR Total Tissues'

FROM Filteredgems_Referral AS Ref INNER JOIN
FilteredBusinessUnit AS BU ON Ref.owningbusinessunit = BU.businessunitid INNER JOIN
Filteredgems_recovery AS Rec ON Ref.gems_referralid = Rec.gems_originatingrefferalid INNER JOIN
Filteredgems_MedicalReview AS MR ON Rec.gems_recoveryid = MR.gems_originatingrecoveryid INNER JOIN
Filteredgems_TissueDetail AS TE ON MR.gems_medicalreviewid = TE.gems_medicalreviewidid

WHERE (Ref.gems_deathnotificationdatetime IS NOT NULL)
AND (Rec.gems_recoveryintent IN ('1'))
AND (DATENAME(year, Ref.gems_deathnotificationdatetime) = '2017')
and TE.statecode in ('0')
) - (

SELECT -- Rec.gems_preservationmediaexpirationdate,Rec.gems_name
--COUNT(DISTINCT TE.gems_tissuedetailid) AS [Total Tissues],
COUNT(DISTINCT CASE WHEN DATENAME(month, Ref.gems_deathnotificationdatetime) = 'April' THEN TE.gems_tissuedetailid END) AS 'APR Total Tissues'
FROM Filteredgems_Referral AS Ref INNER JOIN
FilteredBusinessUnit AS BU ON Ref.owningbusinessunit = BU.businessunitid INNER JOIN
Filteredgems_recovery AS Rec ON Ref.gems_referralid = Rec.gems_originatingrefferalid INNER JOIN
Filteredgems_MedicalReview AS MR ON Rec.gems_recoveryid = MR.gems_originatingrecoveryid INNER JOIN
Filteredgems_TissueDetail AS TE ON MR.gems_medicalreviewid = TE.gems_medicalreviewidid

WHERE (Ref.gems_deathnotificationdatetime IS NOT NULL)
AND (Rec.gems_recoveryintent IN ('1'))
AND (DATENAME(year, Ref.gems_deathnotificationdatetime) = '2017')
AND Rec.gems_preservationmediaexpirationdate > GETDATE()

) as 'Expired Tissue'

解决方案

Ok I've found a very simple example to demonstrate what is going wrong with your queries.

Imagine a simple table like this:

CREATE TABLE [dbo].[Orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [nchar](5) NULL,
	[OrderDate] [date] NULL
) ON [PRIMARY]

INSERT INTO ORDERS VALUES('VINET','1996-Jul-4')
INSERT INTO ORDERS VALUES('VINET','1996-Aug-6')
INSERT INTO ORDERS VALUES('VINET','1996-Sep-2')
INSERT INTO ORDERS VALUES('VINET','1997-Nov-11')
INSERT INTO ORDERS VALUES('VINET','1997-Nov-12')

Now imagine I want to get some differences between some counts like this:

SELECT 
(
	SELECT COUNT(distinct OrderID) AS A, COUNT( distinct CustomerID) AS B
	FROM ORDERS
)-(
	SELECT count(distinct OrderID) AS A, COUNT(distinct CustomerID) AS B
	FROM ORDERS WHERE DATEPART(YEAR, OrderDate) = 1996
)

I get the same error as you

Quote:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

BUT if I only count one item it works: 

SELECT 
(
	SELECT count(distinct OrderID) AS A
	FROM ORDERS
)-(
	SELECT count(distinct OrderID) AS A
	FROM ORDERS WHERE DATEPART(YEAR, OrderDate) = 1996
)

Despite what the error message says we can still get the values we want by using Common Table Expressions, or by using the sub-queries in a subtly different way.
I'm going to add an extra column to each sub-query:

SELECT COUNT(distinct OrderID) AS A, COUNT( distinct CustomerID) AS B, 1 AS C
FROM ORDERS

and 

SELECT count(distinct OrderID) AS A, COUNT(distinct CustomerID) AS B, 1 AS C
FROM ORDERS WHERE DATEPART(YEAR, OrderDate) = 1996

I can now join the results from each sub-query on that extra column e.g. 

;WITH C1 AS
(
	SELECT COUNT(distinct OrderID) AS A, COUNT( distinct CustomerID) AS B, 1 AS C
	FROM ORDERS
), C2 AS
(
	SELECT count(distinct OrderID) AS A, COUNT(distinct CustomerID) AS B, 1 AS C
	FROM ORDERS WHERE DATEPART(YEAR, OrderDate) = 1996
)
SELECT C1.A-C2.A, C1.B-C2.B
FROM C1
INNER JOIN C2 ON C1.C=C2.C

OR if you prefer sub-queries to CTEs

SELECT C1.A-C2.A, C1.B-C2.B
FROM (
	SELECT COUNT(distinct OrderID) AS A, COUNT( distinct CustomerID) AS B, 1 AS C
	FROM ORDERS
) AS C1
INNER JOIN (
	SELECT count(distinct OrderID) AS A, COUNT(distinct CustomerID) AS B, 1 AS C
	FROM ORDERS WHERE DATEPART(YEAR, OrderDate) = 1996
) AS C2 ON C1.C=C2.C

So your query is going to look something like this:

SELECT  C1.[APR Total Tissues] - C2.[APR Total Tissues], C1.[another count] - C2.[another count]
FROM (

 SELECT 1 AS JoinColumn, 
 COUNT(what ever else you want to count) AS [another count],
 COUNT(DISTINCT CASE WHEN DATENAME(month, Ref.gems_deathnotificationdatetime) = 'April' THEN TE.gems_tissuedetailid END) AS 'APR Total Tissues'

 FROM Filteredgems_Referral AS Ref INNER JOIN
 FilteredBusinessUnit AS BU ON Ref.owningbusinessunit = BU.businessunitid INNER JOIN
 Filteredgems_recovery AS Rec ON Ref.gems_referralid = Rec.gems_originatingrefferalid INNER JOIN
 Filteredgems_MedicalReview AS MR ON Rec.gems_recoveryid = MR.gems_originatingrecoveryid INNER JOIN
 Filteredgems_TissueDetail AS TE ON MR.gems_medicalreviewid = TE.gems_medicalreviewidid 

 WHERE (Ref.gems_deathnotificationdatetime IS NOT NULL)
 AND (Rec.gems_recoveryintent IN ('1')) 
 AND (DATENAME(year, Ref.gems_deathnotificationdatetime) = '2017')
 and TE.statecode in ('0')
 ) AS C1 
 INNER JOIN (

 SELECT 1 AS JoinColumn, 
 COUNT(what ever else you want to count) AS [another count],
 COUNT(DISTINCT CASE WHEN DATENAME(month, Ref.gems_deathnotificationdatetime) = 'April' THEN TE.gems_tissuedetailid END) AS 'APR Total Tissues'
 FROM Filteredgems_Referral AS Ref INNER JOIN
 FilteredBusinessUnit AS BU ON Ref.owningbusinessunit = BU.businessunitid INNER JOIN
 Filteredgems_recovery AS Rec ON Ref.gems_referralid = Rec.gems_originatingrefferalid INNER JOIN
 Filteredgems_MedicalReview AS MR ON Rec.gems_recoveryid = MR.gems_originatingrecoveryid INNER JOIN
 Filteredgems_TissueDetail AS TE ON MR.gems_medicalreviewid = TE.gems_medicalreviewidid 

 WHERE (Ref.gems_deathnotificationdatetime IS NOT NULL)
 AND (Rec.gems_recoveryintent IN ('1')) 
 AND (DATENAME(year, Ref.gems_deathnotificationdatetime) = '2017')
 AND Rec.gems_preservationmediaexpirationdate > GETDATE()

 ) as C2 ON C1.JoinColumn=C2.JoinColumn


这篇关于两个不在select query.it中工作的计数函数给出了以下错误，因为“当子查询没有用EXISTS引入时，可以在选择列表中指定一个表达式”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆