sql - SQL Server 从两个表中获取多行数据
问题描述
所以这是我正在使用的两张表
库存项目：
ItemID SKU MSKU
-------------------- ------------------------------ -------------------------
36414 SWR0060
14859140 4220.12.010 220.12.010
14860211 6220.12.010 220.12.010
47717413 6000.22.010 6000.22.XXX
102244764 SWR-SPIGOT SWR-SPIGOTS
115377648 SWR-SPIGOTBLA SWR-SPIGOTS
115381142 SWR-SPIGOTCOP SWR-SPIGOTS
116112478 6000.22.030 6000.22.XXX
116112701 6000.22.050 6000.22.XXX
119263276 SWR-SPIGOTAG SWR-SPIGOTS
库存项目备注：
ItemID MemoText
-------------------- ----------------------------------------
36414 Title:Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool
14859140 Title:Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
47717413 Title:52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
102244764 Title:Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
目前，当我运行下面的查询时，会显示标题直接连接到它们的主要项目的标题，但通过 MSKU 字段连接到这些项目的项目显示 NULL
DECLARE @title as VARCHAR(MAX) = 'Title:';
SELECT
ItemID,
Code as SKU,
AnalysisCode12 as MSKU,
(SELECT TOP 1 MemoText FROM StockItemMemo WHERE StockItemMemo.ItemID = StockItem.ItemId and MemoText like @title+'%') as Title
From StockItem WHERE AnalysisCode7 = 'YES'
ItemID SKU MSKU Title
-------------------- ------------------------------ ------------------------------------------------------------ ----------------------------------------------
36414 SWR0060 Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool
14859140 4220.12.010 220.12.010 Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
14860211 6220.12.010 220.12.010 NULL
47717413 6000.22.010 6000.22.XXX 52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
102244764 SWR-SPIGOT SWR-SPIGOTS Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
115377648 SWR-SPIGOTBLA SWR-SPIGOTS NULL
115381142 SWR-SPIGOTCOP SWR-SPIGOTS NULL
116112478 6000.22.030 6000.22.XXX NULL
116112701 6000.22.050 6000.22.XXX NULL
119263276 SWR-SPIGOTAG SWR-SPIGOTS NULL
我也需要主 SKU 的标题来显示所有具有匹配 MSKU 的项目，所以下面是我想要的结果：
ItemID SKU MSKU Title
-------------------- ------------------------------ ------------------------------------------------------------ ----------------------------------------------
36414 SWR0060 Professional C7 Steel Wire Rope Cutters (0.5mm - 4mm) Snips Fence Tool
14859140 4220.12.010 220.12.010 Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
14860211 6220.12.010 220.12.010 Stainless Steel Base Plate for 42.4mm Balustrade Handrail Railing Component
47717413 6000.22.010 6000.22.XXX 52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
102244764 SWR-SPIGOT SWR-SPIGOTS Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
115377648 SWR-SPIGOTBLA SWR-SPIGOTS Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
115381142 SWR-SPIGOTCOP SWR-SPIGOTS Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
116112478 6000.22.030 6000.22.XXX 52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
116112701 6000.22.050 6000.22.XXX 52mm Stainless Glass Adapter (10-20mm) - Metric and Wood Thread Balustrade Handrail
119263276 SWR-SPIGOTAG SWR-SPIGOTS Stainless Steel Spigot 10-12mm Glass Handrail Balustrade Railing Balcony Clamp
这样做的最佳方法是什么？
解决方案
我相信您可以使用如下查询来实现所需的输出
DECLARE @title as VARCHAR(MAX) = 'Title:';
; WITH CTE AS
(
SELECT
AnalysisCode12 as MSKU,
MemoText as Title
FROM StockItemMemo SIM
JOIN StockItem SI
ON SIM.ItemID = SI.ItemId
WHERE MemoText like @title+'%'
)
SELECT
ItemID,
Code as SKU,
AnalysisCode12 as MSKU,
C.Title
From StockItem SI
LEFT JOIN CTE C
ON C.MSKU=SI.AnalysisCode12
WHERE AnalysisCode7 = 'YES'