SQL 複雜查詢請教

sql mssql 程式邏輯 database

uf023630 2022-05-03 23:52:47 ‧ 867 瀏覽

分享至

我的問題是我有兩張表[Products] & [Package] 其中 [Products] 大約 13,000 筆資料,[Package] 大約 20萬筆
每一個或貨號[Article]在表[products]皆為唯一值，在[package]表中同一貨號可能有多達20種包裝與對應得數量
我需要將兩表合併輸出，下面是我想到的方式，當然他的運行時間也是天荒地老，想請問各位先進，是否有更有效率的語法

SELECT E.[ArticleNo],E.[Name],E.[Unit_Package],E.[Unit_Qty]
	,E.[Order_Package],E.[Order_Qty],E.[Customer_Package],F.[Qty]AS [Customer_Qty]
FROM(


SELECT C.[ArticleNo],C.[Name],C.[Unit_Package],C.[Unit_Qty]
	,C.[Order_Package],D.[Qty] As [Order_Qty],C.[Customer_No],C.[Customer_Package]
FROM(
SELECT A.[ArticleNo],A.[Name],A.[Unit_Package],B.[Qty] As [Unit_Qty],A.[Ouder_No]
	,A.[Order_Package],A.[Customer_No],A.[Customer_Package]
FROM (
	SELECT [ArticleNo],[Name],[ArticleNo] + [Unit_Package] AS [Unit_No]
		,[Unit_Package],[ArticleNo] + [Order_Package] AS [Ouder_No]
		,[Order_Package],[ArticleNo] + [Customer_Package] AS [Customer_No]
		,[Customer_Package]
	FROM [Products]
	) A
JOIN (
	SELECT [ArticleNo] + [Package] AS [Article_PK]
		,[Qunantity] AS[Qty]
	FROM {Package]
	) B On A.[Unit_No] = B.[Article_PK]) C
JOIN (
	SELECT [ArticleNo] + [Package] AS [Article_PK]
		,[Qunantity] AS[Qty]
	FROM {Package]
	) D On C.[Ouder_No] = D.[Article_PK]) E
JOIN (
	SELECT [ArticleNo] + [Package] AS [Article_PK]
		,[Qunantity] AS[Qty]
	FROM {Package]
	) F On E.[Customer_No] = F.[Article_PK]

Products

| ArticleNo | Name |Unit_Package | Order_Package | Customer_Package |
| -------- | ---- | ----- | ---- |
| A0001 |PXESX | CUT | CRT | PC |
| A0002 |GASE| PGK | CRT | PCE
| A0003 |DORR| FCG | FCK | PC

Package

ArticleNo	Package	Quantity
A0001	CUT	2
A0001	CRT	3
A0001	PC	4
A0001	PGK	5
A002	CUT	34
A002	PGK	22
A002	CRT	3
A002	PCE	3
A003	GG	3
A003	FCG	2
A003	FCK	1
A003	PC	7

輸出

| ArticleNo |Name| Unit_Package |Unit_Qty | Order_Package |Order_Qty| Customer_Package | Customer_Qty|
| -------- |---- | ----- | ---- |
| A0001 |PXESX| CUT | 2| CRT |3 | PC |4
| A0002 |GASE| PGK |22 | CRT |3 | PCE|3
| A0003 |DORR| FCG |2 | FCK |1 | PC|1

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2022-05-04 07:45:14

CREATE TABLE Products (
ArticleNo NVARCHAR(50) NULL,
Name NVARCHAR(50) NULL,
Unit_Package NVARCHAR(50) NULL,
Order_Package NVARCHAR(50) NULL,
Customer_Package NVARCHAR(50) NULL);

INSERT INTO Products (ArticleNo,Name,Unit_Package,Order_Package,Customer_Package) VALUES
('A0001','PXESX','CUT','CRT','PC'),
('A0002','GASE','PGK','CRT','PCE'),
('A0003','DORR','FCG','FCK','PC');

CREATE TABLE Package (
ArticleNo NVARCHAR(50) NULL,
Package NVARCHAR(50) NULL,
Quantity INT NULL);

INSERT INTO Package (ArticleNo,Package,Quantity) VALUES
('A0001','CUT',2),
('A0001','CRT',3),
('A0001','PC',4),
('A0001','PGK',5),
('A0002','CUT',34),
('A0002','PGK',22),
('A0002','CRT',3),
('A0002','PCE',3),
('A0003','GG',3),
('A0003','FCG',2),
('A0003','FCK',1),
('A0003','PC',7);

SELECT A.ArticleNo,A.Name,
A.Unit_Package,B.Quantity AS Unit_Qty,
A.Order_Package,C.Quantity AS Order_Qty,
A.Customer_Package,D.Quantity AS Customer_Qty
FROM Products A
LEFT JOIN Package B ON B.ArticleNo=A.ArticleNo AND B.Package=A.Unit_Package
LEFT JOIN Package C ON C.ArticleNo=A.ArticleNo AND C.Package=A.Order_Package
LEFT JOIN Package D ON D.ArticleNo=A.ArticleNo AND D.Package=A.Customer_Package

Demo

回應
分享
檢舉

登入發表回應

石頭

iT邦高手 1 級 ‧ 2022-05-04 09:23:31

借用 rogeryao 大大的 sqlfiddle

另一種方式可以使用 JOIN 一次 Package + aggregate condition function 去完成需求

Index 建立在合適欄位上可以很快速運作

SELECT A.ArticleNo,
       A.Name,
       A.Unit_Package,
       SUM(IIF(B.Package = Unit_Package,B.Quantity,0)) AS Unit_Qty,
       A.Order_Package,
       SUM(IIF(B.Package = Order_Package,B.Quantity,0)) AS Order_Qty,
       A.Customer_Package,
       SUM(IIF(B.Package = Customer_Package,B.Quantity,0)) AS Customer_Qty
FROM Products A
LEFT JOIN Package B ON B.ArticleNo=A.ArticleNo AND B.Package IN (A.Unit_Package,A.Order_Package,A.Customer_Package)
GROUP BY 
      A.ArticleNo,
      A.Name,
      A.Unit_Package,
      A.Order_Package,
      A.Customer_Package

sqlfiddle