如何创建一个 SQL 查询以在同一行中包含 MTD 和 YTD 值?

编程

[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

SQL
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 标识符必须以字母或下划线开头

— 如果只从一张表中进行选择,则不需要限定列中的表名

— 如果您要限定列,则使用别名而不是完整的表名来使脚本更具可读性,例如而不是

SQL
select IS_vwSalesbyProductLineComp.sls_amt
from IS_vwSalesbyProductLineComp

使用

SQL
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]

コメント

标题和URL已复制