SQL Server从两个表中抓取多行数据
SQL Server从两个表中抓取多行数据
提问于 2020-09-11 22:22:05
下面是我正在使用的两个表
StockItem：
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-SPIGOTSStockItemMemo：
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字段连接到这些项目的项目显示为空
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做这件事的最好方法是什么？
Stack Overflow用户
回答已采纳
发布于 2020-09-11 22:57:33
我相信您可以使用下面这样的查询来获得所需的输出
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'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63848927
复制相关文章
相似问题
腾讯云开发者
