【解決方法】MTD 値と YTD 値を同じ行に含める 1 つの SQL クエリを作成するにはどうすればよいですか?

プログラミングQA

[ad_1]

現在 SSRS に変換中の Crystal Report があります。 このレポートには、MTD (月から今日までの値) の 3 列と YTD (年から今日までの値) の 3 列があります。 これら 3 つの列を個別に取得することはできますが、データの精度を高めるために、1 つの SQL ステートメントに結合して 1 行に出力する必要があります。 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.

SSRS に変換している現在の Crystal レポート

編集: サンプル dbfiddle を作成しました: DB フィドル – SQL データベース プレイグラウンド[^]

コード2では、ユニオンといくつかを使用しようとしましたが、次のようなエラーが発生します。

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 識別子は文字またはアンダースコアで始まる必要があります

— 1 つのテーブルからのみ選択する場合は、列でテーブル名を修飾する必要はありません。

— 列を修飾する場合は、完全なテーブル名の代わりにエイリアスを使用して、スクリプトを読みやすくします。

SQL
select IS_vwSalesbyProductLineComp.sls_amt
from IS_vwSalesbyProductLineComp

使用

SQL
select splc.sls_amt 
from IS_vwSalesbyProductLineComp splc

— 列名とテーブル名の区切り文字を削除します。区切り文字は必要なく、クエリを読み取れなくなるだけです。

— 区切り文字を使用する場合は、次を使用します。 [ ] つまり、代わりに "IS_vwSalesbyProductLineComp" 使用 [IS_vwSalesbyProductLineComp] – 繰り返しになりますが、より読みやすくなりました

— あなたの発言

引用:

ただし、データの精度を高めるために、それを 1 つの SQL ステートメントに結合する必要があります。

有効じゃない。 使用される SQL ステートメントの数は、データの精度にはまったく関係ありません。 すべてを 1 つのステートメントにまとめることにこだわると、明瞭さ、正確さ (データではなくスクリプトの)、パフォーマンスに悪影響を及ぼす可能性があります。

— これらすべてのサブクエリはまったく必要ありません。さらに、ご覧のとおり、複数の行が返されます。

必要なすべてのデータを個別の行で取得します。単一の SQL ステートメントであることを主張する場合は、共通テーブル式 (CTE – WITH common_table_expression (Transact-SQL) – SQL Server | Microsoft ドキュメント[^] )。

それから ピボット これらの結果から必要なテーブルを取得します – を参照してください。 SQL Server PIVOT 演算子を実際の例でわかりやすく説明[^]

解決策 2

一見すると…あなたのクエリは本当に悪いです:
最も重要な間違いは次のとおりです。 SELECTが多すぎる
メインが 1 つあります SELECT そして4つのサブSELECT

より少ない数を使用できるようにクエリを書き直してみてください。 SELECT
すべてのサブクエリを確認してください。 いずれかのクエリが複数のレコードを返す場合は、そのクエリを改善する必要があります。

それまでの間、これを読んでください。 SQL Server のメッセージ 512「サブクエリが複数の値を返しました」を修正[^]

解決策 5

https://www.google.com/search?q=standale+to+故郷+水+火+復興
しかし、素晴らしい投稿です。この件についてもう少し詳しく書いていただけないでしょうか?
もう少し詳しく教えていただけると大変ありがたいです。

乾杯!

[ad_2]

コメント

タイトルとURLをコピーしました