如何在存储过程中使用子查询？ [英] How to use sub query in stored procedure?

问题描述

我的子查询返回一个值，该值由多个值组成，用逗号分隔。

我的子查询是这样的： -

 选择 ApplGLCd 来自 ebreportstructureho 
 其中 grpname = ' 附录' 

，它的输出是这个：-0034,0035,0064,0026,0030,0031,0056



当我使用存储过程的子查询但它没有给出任何排序错误，但它应该返回一些不幸发生的数据。



我尝试过：

  SELECT   date ，SUM（ clcr-cldr） AS  ClosingCr  FROM  ebgltransaction 
  WHE RE  brncd = '  0002'  AND  glcd  IN （选择 ApplGLCd 来自 ebreportstructureho 
 其中 grpname = ' 附录' ） AND   date   BETWEEN  '  20151001'  AND    20151031' 
  GROUP   BY   date  
  ORDER   BY   date  

解决案

您需要打破你的代码两个部分。首先将通过拆分子查询的返回结果来填充表变量，第二部分将使用此表变量来获取结果。伪代码就像这样

  DECLARE   @ TBL   TABLE  
（
 GL_ID  VARCHAR （ 100 ）
）
 
  INSERT   INTO   @ TBL （GL_ID）
  SELECT 商品
  FROM （选择 ApplGLCd 来自 ebreportstructureho 
 其中 grpname = ' 附录'）T  CROSS   APPLY  dbo.SplitStrings_Moden（T.ApplGLCd，' ，'）
 
  SELECT   date ，SUM（clcr-cldr） AS  ClosingCr 
  FROM  ebgltransaction 
  INNER   JOIN   @ TBL  T  ON  ebgltransaction.glcd = T.GL_ID 
  WHERE  brncd = '  0002' 
  AND   date   BETWEEN  '  20151001'  AND  '  20151031' 
  GROUP   BY  日期 
 订单  BY   date  

请参阅下面的拆分功能

以正确方式拆分字符串 - 或者下一个最佳方式 [ ^ ]

My sub query returns a single value and that value consists of multiple value which are separated by comma.
My sub query is like this:-

select ApplGLCd from ebreportstructureho
where grpname = 'Appendix'

and it's output is this:-0034,0035,0064,0026,0030,0031,0056

When I use the sub query with the Stored Procedure though it don't give any sort of error but it is supposed to return some data which unfortunately not happening.

What I have tried:

SELECT date,SUM(clcr-cldr) AS ClosingCr FROM ebgltransaction  
WHERE brncd='0002' AND glcd IN (select ApplGLCd from ebreportstructureho
where grpname = 'Appendix') AND date BETWEEN '20151001' AND '20151031'
GROUP BY date
ORDER BY date

解决方案

You need to break your code in two parts. First will fill a table variable by splitting returned result of a sub query and the second part will use this table variable for getting result. A pseudo code would be like this

DECLARE @TBL TABLE
( 
   GL_ID VARCHAR(100)
) 

INSERT INTO @TBL (GL_ID)
SELECT Item 
FROM   (select ApplGLCd from ebreportstructureho
where grpname = 'Appendix') T CROSS APPLY dbo.SplitStrings_Moden(T.ApplGLCd,',')

SELECT date,SUM(clcr-cldr) AS ClosingCr 
FROM ebgltransaction 
     INNER JOIN @TBL T ON ebgltransaction.glcd = T.GL_ID
WHERE brncd='0002' 
AND date BETWEEN '20151001' AND '20151031'
GROUP BY date
ORDER BY date

Please refer below for split functions
Split strings the right way - or the next best way[^]

这篇关于如何在存储过程中使用子查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！