[ad_1]
我有一个 Crystal Report,目前正在将其转换为 SSRS。 该报告有 3 列 MTD(月至今值)和 3 列 YTD(年初至今值)。 我可以将这 3 列分开,但为了数据的准确性,我需要将其合并到一个 SQL 语句中以输出到一行中。 我能够使用 SQL 中的一些合并函数使其几乎接近它。 问题是它不允许我使用 group by 命令并引发错误。 我应该按照下面 Crystal Report 中显示的方式编辑 SQL 查询中的哪些内容?
错误 :
Msg 512, Level 16, State 1, Line 106 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
我正在将当前的 Crystal Report 转换为 SSRS
编辑:我刚刚创建了一个示例 dbfiddle : DB Fiddle – SQL 数据库游乐场[^]
在代码 2 中,我尝试使用一些 with 和 union 但它给了我一些错误
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
我尝试过的:
代码1
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 ),0) AS G1MTD, "IS_vwSalesbyProductLineComp"."Mainslspsn_name", "IS_vwSalesbyProductLineComp"."PCSummarized", coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk")FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS Qty_Sold, coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."sls_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_Sales, coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."cost_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_COGS, coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS G2YTD, SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk") AS Qty_Sold, SUM("IS_vwSalesbyProductLineComp"."sls_amt") AS MTD_Sales, SUM("IS_vwSalesbyProductLineComp"."cost_amt") AS MTD_COGS FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month = 12 Group by Mainslspsn_name,PCSummarized order by PCSummarized
代码2
SET ARITHABORT OFF SET ANSI_WARNINGS OFF WITH T1 AS ( SELECT ( SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100) AS G1MTD, IS_vwSalesbyProductLineComp.Mainslspsn_name, IS_vwSalesbyProductLineComp.PCSummarized, ( SELECT SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk) FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS Qty_Sold, ( SELECT SUM(IS_vwSalesbyProductLineComp.sls_amt) FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS YTD_Sales, ( SELECT SUM(IS_vwSalesbyProductLineComp.cost_amt) FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS YTD_COGS, ( SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100 FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS G2YTD, SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk) AS Qty_Sold2, --> renamed because ambigous SUM(IS_vwSalesbyProductLineComp.sls_amt) AS MTD_Sales, SUM(IS_vwSalesbyProductLineComp.cost_amt) AS MTD_COGS FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month = 12 GROUP BY Mainslspsn_name, PCSummarized ), T2 AS ( SELECT 0 AS C1, '' AS C2, '' AS C3, 0 AS C4, 0 AS C5, 0 AS C6, 0 AS C7, 0 AS C8, 0 AS C9, 0 AS C10 ), T3 AS ( SELECT * FROM T1 UNION SELECT * FROM T2 ) SELECT SUM(G1MTD) AS G1MTD, (SELECT [text()] = Mainslspsn_name FROM "100".dbo.IS_vwSalesbyProductLineComp FOR XML PATH(''), TYPE ).value ( './text()[1]', 'nvarchar(max)' ), (SELECT [text()] = PCSummarized FROM "100".dbo.IS_vwSalesbyProductLineComp FOR XML PATH(''), TYPE ).value ( './text()[1]', 'nvarchar(max)' ), SUM(Qty_Sold) AS Qty_Sold, SUM(YTD_Sales) AS YTD_Sales, SUM(YTD_COGS) AS YTD_COGS, SUM(G2YTD) AS G2YTD, SUM(Qty_Sold2) AS Qty_Sold2, SUM(MTD_Sales) AS MTD_Sales, SUM(MTD_COGS) AS MTD_COGS FROM T3 GROUP BY Mainslspsn_name, PCSummarized ORDER BY PCSummarized;
解决方案1
在没有任何样本数据的情况下,首先解决这些问题。
— 100.dbo.IS_vwSalesbyProductLineComp 不是有效的 SQL 标识符。 SQL 标识符必须以字母或下划线开头
— 如果只从一张表中进行选择,则不需要限定列中的表名
— 如果您要限定列,则使用别名而不是完整的表名来使脚本更具可读性,例如而不是
select IS_vwSalesbyProductLineComp.sls_amt from IS_vwSalesbyProductLineComp
使用
select splc.sls_amt from IS_vwSalesbyProductLineComp splc
— 去掉列名和表名上的分隔符 – 它们不是必需的,只会使您的查询不可读
— 如果您确实使用分隔符,则使用 [ ] 即代替 "IS_vwSalesbyProductLineComp"
使用 [IS_vwSalesbyProductLineComp]
– 再次,它更具可读性
——你的陈述
引用:但为了数据的准确性,我需要将其合并到一个 SQL 语句中
无效。 使用的 SQL 语句数量与数据准确性没有任何关系。 痴迷于将所有内容都放入一个语句中可能会损害清晰度、准确性(脚本的准确性,而不是数据的准确性)和性能。
— 绝对不需要所有这些子查询 – 另外,正如您所看到的,您将返回多行
在单独的行中获取所需的所有数据 – 如果您坚持将其作为单个 SQL 语句,则使用通用表表达式 (CTE – WITH common_table_expression (Transact-SQL) – SQL Server | SQL Server 2015 微软文档[^] )。
然后 枢 这些结果可以获得您想要的表格 – 请参阅 通过实际示例清晰解释 SQL Server PIVOT 运算符[^]
解决方案2
乍一看…你的查询真的很糟糕:
最重要的错误是: 太多的 SELECT!
你有 1 个主要 SELECT
然后4个子SELECT
!
尝试重写您的查询以能够使用更少的数量 SELECT
。
检查每个子查询。 如果其中任何一个返回多条记录,则必须改进该查询。
与此同时,请阅读以下内容: 修复 SQL Server 中的消息 512“子查询返回超过 1 个值”[^]
解决方案5
https://www.google.com/search?q=standale+to+Hometown+Water+Fire+Restoration
不过,这篇文章很棒,我想知道你是否可以就这个主题多写一点?
如果您能详细说明一下,我将不胜感激。
干杯!
[ad_2]
コメント