From 7ff441cde857c41ae9908100de9c469ed5ab76be Mon Sep 17 00:00:00 2001
From: zc <zc>
Date: 星期一, 29 七月 2024 19:11:56 +0800
Subject: [PATCH] 视图sql

---
 src/main/java/asr_show_diff.sql |   87 +++++++++++++++++++++++++------------------
 1 files changed, 50 insertions(+), 37 deletions(-)

diff --git a/src/main/java/asr_show_diff.sql b/src/main/java/asr_show_diff.sql
index f06fdb0..35e983a 100644
--- a/src/main/java/asr_show_diff.sql
+++ b/src/main/java/asr_show_diff.sql
@@ -1,44 +1,57 @@
 SELECT SUM
-       ( asrsQty ) asrsQty,
-       SUM ( erpQty ) erpQty,
-       matnr,
-       specs,
-       maktx
+	( asrsQty ) asrsQty,
+	SUM ( erpQty ) erpQty,
+	matnr,
+	maktx,
+	pgNo,
+	outOrderNo,
+	batch,
+	model ,specs
 FROM
-    (
-        SELECT CAST
-               ( SUM ( anfme ) AS INT ) asrsQty,
-               0 AS erpQty,
-               matnr,
-               specs,
-               maktx
-        FROM
-            asr_loc_detl
-        GROUP BY
-            matnr,
-            specs,
-            maktx UNION ALL
-        SELECT
-            0,
-            CAST ( SUM ( numis ) AS INT ),
-            itemid,
-            itemcode,
-            itemname
-        FROM
-            (
-                SELECT
-                    *
-                FROM
-                    OPENROWSET ( 'SQLOLEDB', '192.168.1.147,1800'; 'XGMLK'; 'xunda0413', XDLinkLK.dbo.vwErpLKStorage )
-		WHERE
-			LKName = '涓壃' 
-		) a 
+	(
+	SELECT CAST
+		( SUM ( anfme ) AS INT ) asrsQty,
+		0 AS erpQty,
+		matnr,
+		model,
+		maktx,
+		pg_no AS pgNo,
+		out_order_no AS outOrderNo,
+		batch ,specs
+	FROM
+		asr_loc_detl
+	GROUP BY
+		matnr,
+		maktx,
+		pg_no,
+		out_order_no,
+		batch,
+		model,specs UNION ALL
+	SELECT
+		0,
+		CAST ( SUM ( numis ) AS INT ),
+		itemid,
+		itemcode,
+		itemname,
+		'' AS pgNo,
+		'' AS outOrderNo,
+		itembatch AS batch
+		,itemSpc specs
+	FROM
+		( SELECT * FROM OPENROWSET ( 'SQLOLEDB', '192.168.1.147,1800'; 'XGMLK'; 'xunda0413', XDLinkLK.dbo.vwErpLKStorage ) WHERE LKName = '涓壃浜屾湡' ) a
 	GROUP BY
 		itemid,
 		itemcode,
-		itemname 
-	) b WHERE 1=1 and matnr not in (select matnr from man_order_detl)
+		itemname,
+		itembatch ,itemSpc
+	) b
+WHERE
+	1 = 1
+	AND matnr NOT IN ( SELECT matnr FROM man_order_detl )
 GROUP BY
 	matnr,
-	specs,
-	maktx
\ No newline at end of file
+	model,
+	maktx,
+	pgNo,
+	outOrderNo,
+	batch,specs
\ No newline at end of file

--
Gitblit v1.9.1