您的位置: 首页  >  技术问答  >  我的INNER SELECT没有提示正确的输出

我的INNER SELECT没有提示正确的输出

分类: 技术问答 • 2022-05-31 15:15:08
问题描述：

自从我做这份报告已经过了2个星期。我需要今天完成，但仍然无法找到任何解决方案。我知道我的错误发生在哪里（它在我的INNER SELECT没有GROUP BY ItemCode），但是我无法通过INNER SELECT上的ItemCode对它进行分组，因为我的OUTER SELECT上有GROUP BY。我的INNER SELECT没有提示正确的输出

我将有一个错误在我的代码，如果我把GROUP BY内部查询内部和错误是：！ “子查询返回多个值，这不是当子查询遵循=，=允许， <，< =，>，> =或当子查询用作表达“

这是我的代码，它正在运行，但折旧金额高达每月全年所有的值。

DROP PROCEDURE dfsi_sp_FALS2 
GO 
CREATE PROCEDURE dfsi_sp_FALS2 
@filterDate DATE 
AS 


SELECT ODPV.ItemCode, OITM.ItemName, ITM8.APC, ITM7.UsefulLife, SUM(ODPV.OrdDprPost) AS AccumulatedDep, 
(ITM8.APC - SUM(ODPV.OrdDprPost)) AS NBV, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-01-01 00:00:00' AND 
ODPV.ToDate = '2014-01-31 00:00:00' GROUP BY ODPV.ItemCode) AS DateofJanuary, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-02-01 00:00:00' AND 
ODPV.ToDate = '2014-02-28 00:00:00') AS DateofFebruary, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-03-01 00:00:00' AND 
ODPV.ToDate = '2014-03-31 00:00:00') AS DateofMarch, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-04-01 00:00:00' AND 
ODPV.ToDate = '2014-04-30 00:00:00') AS DateofApril, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-05-01 00:00:00' AND 
ODPV.ToDate = '2014-05-31 00:00:00') AS DateofMay, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-06-01 00:00:00' AND 
ODPV.ToDate = '2014-06-30 00:00:00') AS DateofJune, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-07-01 00:00:00' AND 
ODPV.ToDate = '2014-07-31 00:00:00') AS DateofJuly, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-08-01 00:00:00' AND 
ODPV.ToDate = '2014-08-31 00:00:00') AS DateofAugust, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-09-01 00:00:00' AND 
ODPV.ToDate = '2014-09-30 00:00:00') AS DateofSeptember, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-10-01 00:00:00' AND 
ODPV.ToDate = '2014-10-31 00:00:00') AS DateofOctober, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-11-01 00:00:00' AND 
ODPV.ToDate = '2014-11-30 00:00:00') AS DateofNovember, 
(SELECT SUM(ODPV.OrdDprPost) FROM ODPV WHERE ODPV.FromDate = '2014-12-01 00:00:00' AND 
ODPV.ToDate = '2014-12-31 00:00:00') AS DateofDecember 


FROM ODPV 
INNER JOIN OITM ON OITM.ItemCode = ODPV.ItemCode  
INNER JOIN ITM7 ON ITM7.ItemCode = ODPV.ItemCode 
INNER JOIN ITM8 ON ITM8.ItemCode = ODPV.ItemCode 

WHERE 
ODPV.DprArea = 'Main Area' AND ODPV.PeriodCat = @filterDate 
GROUP BY ODPV.ItemCode, OITM.ItemName, ITM8.APC, ITM7.UsefulLife 

GO 

EXEC dfsi_sp_FALS2 '2014';

每月折旧列将re SULT到这一点，如果我运行上面的查询：

Date of September: 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566 
432803.387566

此代码是我需要什么，但我不能做我的查询，因为这个代码应该是在内部的选择：

SELECT SUM(ODPV.OrdDprPost) AS DateofSeptember FROM ODPV WHERE ODPV.FromDate = '2014-10-01 00:00:00' 
AND ODPV.ToDate = '2014-10-31 00:00:00' GROUP BY ODPV.ItemCode;

上面的代码将导致这个（我想要的结果（这是每个ItemCode上月折旧））：

10200.000000 
0.000000 
0.000000 
2921.000000 
498.000000 
1030.000000 
0.000000 
0.000000 
427.000000 
327.000000 
0.000000 
291.000000 
30.000000 
13.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
1899.000000 
0.000000 
1439.000000 
28.000000 
190.000000 
18.000000 
516.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
121.000000 
121.000000 
354.000000 
382.000000 
32.000000 
588.000000 
127.000000 
88.000000 
88.000000 
0.000000 
0.000000 
388.000000 
340.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
79.000000 
457.000000 
457.000000 
167.000000 
181.000000 
213.000000 
107.000000 
82.000000 
81.000000 
135.000000 
0.000000 
217.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
35.000000 
35.000000 
35.000000 
43.000000 
18.000000 
18.000000 
2.000000 
50.000000 
174.000000 
53.000000 
29.000000 
26.000000 
48.000000 
333.000000 
27.000000 
662.000000 
1660.000000 
1109.000000 
154.000000 
112.000000 
218.000000 
423.000000 
85.000000 
265.000000 
221.000000 
134.000000 
1349.000000 
21.000000 
34.000000 
54.000000 
0.000000 
121.000000 
128.000000 
83.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
616.000000 
614.000000 
1666.000000 
0.000000 
0.000000 
0.000000 
469.000000 
1120.000000 
9.000000 
20.000000 
99.000000 
99.000000 
152.000000 
18.000000 
656.000000 
108.000000 
84.000000 
84.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
1188.000000 
59.000000 
0.000000 
90.000000 
90.000000 
86.000000 
86.000000 
29.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
0.000000 
934.000000 
934.000000 
164.000000 
0.000000 
0.000000 
0.000000 
0.000000 
104.000000 
338.000000 
338.000000 
338.000000 
284.000000 
254.000000 
35.000000 
45.000000 
45.000000 
45.000000 
168.000000 
162.000000 
162.000000 
273.000000 
275.000000 
32.000000 
609.000000 
72.000000 
72.000000 
0.000000 
0.000000 
51.000000

我知道为什么它总结了，但我不能GROUP BY我的内部查询，因为它会有一个错误。

我需要你的帮助家伙pleaseeee。非常感谢你。我会很感激你的建议。

+0

可能是您的内部查询返回多个值 – 2015-02-11 05:14:40

+0

错误：“子查询返回的值不止1个，当子查询跟随=，！=，，> =或子查询用作表达。”我会在我的内部查询中放置一个组时提示。我需要通过内部查询进行分组，以便获得正确的结果或者有其他选择？ – 2015-02-11 05:24:10

答

不知道究竟是什么问题。如果你有简化的要求，如简单的例子，这将是容易的...

根据我的理解，你试图为每个项目代码获得每月的总和。如果是，则尝试下面给出的查询。

SELECT Od.ItemCode, OITM.ItemName, ITM8.APC, ITM7.UsefulLife, SUM(Od.OrdDprPost) AS AccumulatedDep, 
(ITM8.APC - SUM(Od.OrdDprPost)) AS NBV, 
(SELECT SUM(OdJ.OrdDprPost) FROM ODPV OdJ WHERE OdJ.FromDate = '2014-01-01 00:00:00' AND OdJ.ToDate = '2014-01-31 00:00:00' AND OdJ.ItemCode = Od.ItemCode) AS DateofJanuary, 
(SELECT SUM(OdF.OrdDprPost) FROM ODPV OdF WHERE OdF.FromDate = '2014-02-01 00:00:00' AND OdF.ToDate = '2014-02-28 00:00:00' AND OdF.ItemCode = Od.ItemCode) AS DateofFebruary  

FROM ODPV Od 
INNER JOIN OITM ON OITM.ItemCode = Od.ItemCode  
INNER JOIN ITM7 ON ITM7.ItemCode = Od.ItemCode 
INNER JOIN ITM8 ON ITM8.ItemCode = Od.ItemCode 

WHERE 
Od.DprArea = 'Main Area' AND Od.PeriodCat = @filterDate 
GROUP BY Od.ItemCode, OITM.ItemName, ITM8.APC, ITM7.UsefulLife

我只给了Jan和FEB。只需复制每个月的内部查询并更改子查询中的日期和别名即可。

+0

首先非常感谢您的鼎力帮助我的朋友。 （SELECT SUM（OdJ.OrdDprPost）FROM ODPV OdJ WHERE OdJ.FromDate ='2014-01-01 00:00:00'AND OdJ.ToDate ='2014-01-31 00:00:00'Where OdJ。 ItemCode = Od.ItemCode）AS Dateof January， 有两个（2）WHERE子句。我有一个错误。你的语法正确吗？我希望你的查询能够运行。因为我现在需要完成这个。再次非常感谢你的回答。 – 2015-02-11 05:35:00

+0

我编辑它。第二个地方应该用'AND'代替。 – Shamseer 2015-02-11 05:54:27

+0

我发现它是AND。嘿，我的朋友，非常感谢！你救了我的一天！ :) 祝你有美好的一天。 – 2015-02-11 05:56:08

答

只是一个建议.. 尝试在Proc中创建一个临时表并转储其中的所有内部选择查询输出，并将临时表连接到其他表以获得整体输出。

+0

嘿，我的朋友。谢谢你的建议。 我一直在想这个。但我不知道如何开始做。但是我很乐意学习，所以下次我会这样做。这份报告需要紧急，所以我更喜欢第一个解决方案。 非常感谢您的兄弟！这也有助于我。 :) – 2015-02-11 05:59:32

相关推荐