运行时发现此错误 - 当EXISTS未引入子查询时，只能在选择列表中指定一个表达式。 [英] find this error when running - Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

问题描述

 选择 
 min（h。[Spcl 订单]），
（选择（ case  当 sum（pro。[Quantity]）   null  然后  0   else  sum（pro。[Quantity]） END ））
 + 
（选择（ case  当 sum（A. [Quantity]）   null  然后  0   else  sum（A。[Quantity]） END ），
 min（b。[发布日期]），min（c。[订单否]），min（d。[Prod_Order No]），min（e。[No]），
 min（f。[项目类别代码]），min（g。 [位置代码]），min（i。[Spcl Order_Purch]）
 来自 [tablename2] A 
 内部  join  [样本]。[dbo]。[发布日期_PRL] 为 b   a。[发布日期] = b。[发布日期] 
 内部 加入 [样本]。[dbo]。[< span class =code-keyword> Order  No_PRL]  as  c  on  a。[ 订单否_] = c。[订单否] 
 内部  join  [样本]。[dbo]。[Prod_ OrderNo_PRL]  as  d  on  a。[Prod_ 订单否_] = d。[Prod_OrderNo] 
  inner   join  [sample]。[dbo]。[No_PRL]  as  e  on  a。[No _] = e。[No] 
  inner   join  [sample]。[dbo]。[Item Category Code_PRL]  as  f  on  a。[项目类别代码] = f。[项目类别代码] 
 内部  join  [样本]。[dbo]。[位置Code_PRL]   g   a。[位置代码] = g 。[位置代码] 
 内部  join  [样本]。[dbo]。[Spcl Order_PRL ]  as  i  on  a。[Spcl  Order ]=i.[Spcl Order_Purch] 
 其中（（A. [Item Category Code] =  '  STYLES'）和（A. [Prod_ 订单否_] = ' '）和（A. [来自供应商No_]  （'  S02052'）） AND （A. [Quantity]> 0）
  AND （A. [发布日期] =亲。[完成日期 ]）））
 
 来自 [tablename1] pro 
  inner   join  [sample]。[dbo]。[Spcl Order_PO]  as  h  on  Pro。[S pcl 订单] = h。[Spcl 订单] 
  where （（pro。[Status] = 4）和（亲[完成日期] = '  2013-10-10'））    pro。[已完成日期] 

解决方案

我认为这就是你所追求的：

  SELECT  *  FROM  
（
 选择 
 pro 。[完成日期] finished_date，
 min（h。[Spcl 订单]）Spcl_Order， 
  case  当 sum（pro。[Quantity]）是  null  然后  0   else  sum（pro。[Quantity]） END  pro_qty 
  from  [tablename1] pro 
  inner   join  [样本] 。[dbo]。[Spcl Order_PO]  as  h  on  Pro。[Spcl 订单] = h。[Spcl 订单] 
 其中​​ （（亲。[状态] = 4）和（亲[完成日期] = '  2013-10-10'））
  group   by  pro。[已完成日期] 
）为 pro1 
  INNER   JOIN  
（
 选择 
 A. [发布日期] posting_date 
  case  当 sum（A. [Quantity]）   null     0   else  sum（A。[Quantity]） END  other_qty，
 min（b。[Posting  Date ]）min_posting_date，
 min（c。[订单否]）min_order，
 min（d。[Prod_Order No]） min_prod_order，
 min（e。[No]）min_no，
 min（f。[Item Category Code]）min_cat_code，
 min（g。[Location Code]）min_loc_code，
 min（i。[Spcl Order_Purch]）min_order_purch 
 来自 [tablenam e2] A 
 内部  join  [样本]。[dbo]。[Posting Date_PRL] < span class =code-keyword> as  b  on  a。[发布日期] = b。[发布日期] 
 内部 加入 [样本]。[dbo]。[订单 No_PRL]  as  c   a。[订单否_] = c。[订单否] 
 内部  join  [样本]。[dbo]。[Prod_ OrderNo_PRL] < span class =code-keyword> as  d  on  a。[Prod_ 订单 No_ ] = d。[Prod_OrderNo] 
 内部  join  [样本]。[dbo]。[ No_PRL ]  as  e  on  a。[No _] = e。[No] 
 内部  join  [样本]。[dbo]。[项目类别Code_PRL]  as  f  on  a。[项目类别代码] = f。[项目类别代码] 
 内部  join  [样本]。[dbo]。[Location Code_PRL]  as  g 上的class =code-keyword> a。[位置代码] = g。[位置代码] 
 内部  join  [sample]。[dbo]。[Spcl Order_PRL]  as  i  on  a。[Spcl 订单] = i。[Spcl Order_Purch] 
 其中（ A. [项目类别代码] = '  STYLES'）和（A. [Prod_ 订单否_] = ' '）和（A. [买方供应商No_]  in （'  S02052'）） AND （A. [数量]> 0）
  GROUP   BY  A. [发布日期]）
） as 发​​布
  ON  pro1.finished_date = post.posting_date 

BTW，如果只留下选择，你仍然可以使用你的查询（A. [数量]）为空，然后在您的子查询中为0否则求和（A. [数量]）END），并删除所有其他聚合（min（b。[Posting Date]） ，min（c。[订单号]），min（d。[Prod_Order No]），min（e。[No]），

min（f。[Item Category Code]），min （克。[定位Code]），min（i。[Spcl Order_Purch]）），即这个查询也可以工作：

 选择 
 min（h。[Spcl 订单]），
  case  当 sum（pro。[Quantity]）   null  然后  0   else  sum（pro。[Quantity]） END  
 + 
（ select （ case  当 sum（A. [Quantity]）是  null  然后  0   else  sum（A。[Quantity]） END ）
 来自 [ta blename2] A 
 内部  join  [样本]。[dbo]。[Posting Date_PRL] < span class =code-keyword> as  b  on  a。[发布日期] = b。[发布日期] 
 内部 加入 [样本]。[dbo]。[订单 No_PRL]  as  c   a。[订单否_] = c。[订单否] 
 内部  join  [样本]。[dbo]。[Prod_ OrderNo_PRL] < span class =code-keyword> as  d  on  a。[Prod_ 订单 No_ ] = d。[Prod_OrderNo] 
 内部  join  [样本]。[dbo]。[ No_P RL]  as  e  on  a。[No _] = e。[No] 
  inner   join  [sample]。[dbo]。[Item Category Code_PRL]  as  f  on  a。[项目类别代码] = f。[项目类别代码] 
 内部  join  [sample]。[dbo]。[Location Code_PRL]  as  g < span class =code-keyword> on  a。[位置代码] = g。[位置代码] 
 内部  join  [sample]。[dbo]。[Spcl Order_PRL]  as  i  on  a。[Spcl 订单] = i。[Spcl Order_Purch] 
 其中 （（A. [项目类别代码] = '  STYLES'）和（A. [Prod_ 订单否_] = ' '）和（A. [从供应商处购买No_] 在（'  S02052'）） AND （A. [数量]> 0）
  AND （A。[发布日期] =亲。[完成日期]）））
 
 来自 [ tablename1] pro 
  inner   join  [sample]。[dbo]。[Spcl Order_PO] < span class =code-keyword> as  h  on  Pro。[Spcl  Order ] = h。[Spcl 订单] 
 其中（（pro。[Status] = 4） 和 （亲。[完成日期] = '  2013-10 -10'）） group   by  pro。[已完成日期 

select 
min(h.[Spcl Order]),
(select  (case when sum(pro.[Quantity]) is null then 0 else sum(pro.[Quantity]) END))
+
(select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END),
 min(b.[Posting Date]),min(c.[Order No]),min(d.[Prod_Order No]),min(e.[No]),
 min(f.[Item Category Code]),min(g.[Location Code]),min(i.[Spcl Order_Purch])
 from [tablename2] A 
inner join [sample].[dbo].[Posting Date_PRL] as b on a.[Posting Date]=b.[Posting Date]
inner join [sample].[dbo].[Order No_PRL] as c on a.[Order No_]=c.[Order No]
inner join [sample].[dbo].[Prod_ OrderNo_PRL] as  d on a.[Prod_ Order No_]=d.[Prod_OrderNo] 
inner join [sample].[dbo].[No_PRL] as e on a.[No_]=e.[No]
inner join [sample].[dbo].[Item Category Code_PRL] as f on a.[Item Category Code]=f.[Item Category Code]
inner join [sample].[dbo].[Location Code_PRL] as g  on a.[Location Code]=g.[Location Code]
inner join [sample].[dbo].[Spcl Order_PRL] as i on a.[Spcl Order]=i.[Spcl Order_Purch]
where ((A.[Item Category Code] = 'STYLES') and (A.[Prod_ Order No_]='') and ( A.[Buy-from Vendor No_] in ('S02052')) AND (A.[Quantity]>0) 
AND ( A.[Posting Date] = pro.[Finished Date])))
 
from [tablename1] pro 
inner join [sample].[dbo].[Spcl Order_PO] as h on  Pro.[Spcl Order]=h.[Spcl Order]
where ((pro.[Status]=4) and (pro.[Finished Date] ='2013-10-10' ))  group by pro.[Finished Date]

解决方案

I think this is what you are after:

SELECT * FROM
(
	select 
	pro.[Finished Date] finished_date,
	min(h.[Spcl Order]) Spcl_Order,
	case when sum(pro.[Quantity]) is null then 0 else sum(pro.[Quantity]) END pro_qty
	from [tablename1] pro 
	inner join [sample].[dbo].[Spcl Order_PO] as h on  Pro.[Spcl Order]=h.[Spcl Order]
	where ((pro.[Status]=4) and (pro.[Finished Date] ='2013-10-10' ))  
	group by pro.[Finished Date]
) as pro1
INNER JOIN
(
	select 
	 A.[Posting Date] posting_date
	 case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END other_qty,
	 min(b.[Posting Date]) min_posting_date,
	 min(c.[Order No]) min_order,
	 min(d.[Prod_Order No]) min_prod_order,
	 min(e.[No]) min_no,
	 min(f.[Item Category Code]) min_cat_code,
	 min(g.[Location Code]) min_loc_code,
	 min(i.[Spcl Order_Purch]) min_order_purch
	from [tablename2] A 
	inner join [sample].[dbo].[Posting Date_PRL] as b on a.[Posting Date]=b.[Posting Date]
	inner join [sample].[dbo].[Order No_PRL] as c on a.[Order No_]=c.[Order No]
	inner join [sample].[dbo].[Prod_ OrderNo_PRL] as  d on a.[Prod_ Order No_]=d.[Prod_OrderNo] 
	inner join [sample].[dbo].[No_PRL] as e on a.[No_]=e.[No]
	inner join [sample].[dbo].[Item Category Code_PRL] as f on a.[Item Category Code]=f.[Item Category Code]
	inner join [sample].[dbo].[Location Code_PRL] as g  on a.[Location Code]=g.[Location Code]
	inner join [sample].[dbo].[Spcl Order_PRL] as i on a.[Spcl Order]=i.[Spcl Order_Purch]
	where (A.[Item Category Code] = 'STYLES') and (A.[Prod_ Order No_]='') and ( A.[Buy-from Vendor No_] in ('S02052')) AND (A.[Quantity]>0) 
	GROUP BY A.[Posting Date])
)  as post
ON pro1.finished_date = post.posting_date

BTW, you still could use your query if you leave only select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END) in your sub-query, and remove all other aggregates (min(b.[Posting Date]),min(c.[Order No]),min(d.[Prod_Order No]),min(e.[No]),
min(f.[Item Category Code]),min(g.[Location Code]),min(i.[Spcl Order_Purch])), i.e., this query will also work:

select 
min(h.[Spcl Order]),
case when sum(pro.[Quantity]) is null then 0 else sum(pro.[Quantity]) END
+
(select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END)
 from [tablename2] A 
inner join [sample].[dbo].[Posting Date_PRL] as b on a.[Posting Date]=b.[Posting Date]
inner join [sample].[dbo].[Order No_PRL] as c on a.[Order No_]=c.[Order No]
inner join [sample].[dbo].[Prod_ OrderNo_PRL] as  d on a.[Prod_ Order No_]=d.[Prod_OrderNo] 
inner join [sample].[dbo].[No_PRL] as e on a.[No_]=e.[No]
inner join [sample].[dbo].[Item Category Code_PRL] as f on a.[Item Category Code]=f.[Item Category Code]
inner join [sample].[dbo].[Location Code_PRL] as g  on a.[Location Code]=g.[Location Code]
inner join [sample].[dbo].[Spcl Order_PRL] as i on a.[Spcl Order]=i.[Spcl Order_Purch]
where ((A.[Item Category Code] = 'STYLES') and (A.[Prod_ Order No_]='') and ( A.[Buy-from Vendor No_] in ('S02052')) AND (A.[Quantity]>0) 
AND ( A.[Posting Date] = pro.[Finished Date])))
 
from [tablename1] pro 
inner join [sample].[dbo].[Spcl Order_PO] as h on  Pro.[Spcl Order]=h.[Spcl Order]
where ((pro.[Status]=4) and (pro.[Finished Date] ='2013-10-10' ))  group by pro.[Finished Date]

这篇关于运行时发现此错误 - 当EXISTS未引入子查询时，只能在选择列表中指定一个表达式。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！