内部连接的优化查询

本文介绍了内部连接的优化查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 Hello EveryOne 我有两张桌子A和桌子B 表A包含 AID |描述 1 | ABC 2 | DEF 3 | GHI 4 | JKL TableB包含 BID | AIDRefrence 1 | 1 2 | 1 3 | 2 4 | 2 5 | 6 6 | 2 我的输出表必须像这样 TableC AID | BID |描述 1 | 1 | ABC（BID（1,2）可以是AIDRefrence 1中的任何一个） 2 | 3 | DEF（BID（3,4,6）可以是AIDRefrence中的任何一个2） 我的代码就像是 选择 a.AID，b.BID，a.description 从（选择 * 来自 表 B 其中 BID （选择 max（BID）来自 TableB group 按 AIDRefrence））B 内部 加入表A A a。 aid = b.AIDRefrence 只有我面临的问题是TableB包含超过50K行，而在GroupBy上它返回至少15K行。 可以有人优化这个查询。解决方案 我不确定你想要实现什么，但请看下面的例子： DECLARE @ tab1 TABLE （AID INT IDENTITY （ 1 ， 1 ） ，aDescription VARCHAR （ 30 ）） INSERT INTO @ tab1 （aDescription） SELECT ' ABC' UNION ALL SELECT ' DEF' UNION ALL SELECT ' GHI' UNION ALL SELECT ' JKL' DECLARE @ tab2 TABLE （BID INT IDENTITY （ 1 ， 1 ），AIDReference INT ） INSERT INTO @ tab2 （AIDReference） SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION 所有 SELECT 6 UNION ALL SELECT 2 SELECT a.AID，b.BID，a.aDescription FROM @ tab1 AS a INNER JOIN @ tab2 AS b ON a.AID = b .AIDReference 结果： 1 1 ABC 1 2 ABC 2 3 DEF 2 4 DEF 2 6 DEF 更多信息，请参阅： SQL连接的可视化表示 [ ^ ] 如果你想列举一行中每个AID的所有BID，请看： SELECT a.AID，a.aDescription，STUFF（（ SELECT ' ，' + CONVERT （ VARCHAR （ 30 ），b.BID） AS ' text（）' FROM @ tab2 AS b WHERE a.AID = b.AIDReference FOR XML PATH（' '））， 1 ， 1 ，' '） AS [BIDs] FROM @ tab1 AS a GROUP BY a.AID，a.aDescription 结果： AID Desc .. BID 1 ABC 1 ， 2 2 DEF 3 ， 4 ， 6 3 GHI NULL 4 JKL NULL [/ EDIT] 由于TableB仅包含BID和AIDRefrence，因此您的内部查询不必要地复杂。 替换 （从 * > TableB 其中 BID （选择最大值（BID）来自 TableB group 按 AIDRefrence ）） by （ select AIDRefrence as AID，max（BID） as BID 来自 TableB group 按 AIDRefrence）A 顺便提一下，您向我们展示的代码中必定存在一些错误：选择... a.description - a不包含列description，它位于TableA中。 全部谢谢，但我自己解决了这个问题。 声明 @ tbl 表（BID int ） 插入 进入 @ tbl 从最大值（b.BID） / span> TableA a inner join TableB b on a.aid = b.AIDrefrence group 按 b.AIDRefrence 选择 a.AID，b.BID，a.description 来自 tableB B 内部 加入表A A a.aid = b.AIDRefrence 其中 b.bid （从 @ tbl 中选择 * Hello EveryOneI have Two Table TableA and TableBTable A containsAID | Description1 | ABC2 | DEF3 | GHI4 | JKLTableB ContainsBID| AIDRefrence1 | 12 | 13 | 24 | 25 | 66 | 2My Output Table must be like ThisTableCAID | BID | Description1 | 1| ABC (BID(1,2) Can be Any one of AIDRefrence 1)2 | 3| DEF (BID(3,4,6) Can be Any one of AIDRefrence 2)My Code is LikeSelect a.AID, b.BID, a.description From (Select * from Table B where BID in (select max(BID) from TableB group By AIDRefrence )) B inner Join TableA A on a.aid = b.AIDRefrenceOnly Problem That I face is thet TableB contains More then 50K rows and on GroupBy it returns atleast 15K rows.Can Some one Optimize this Query. 解决方案 I'm not sure what you want to achieve, but see below example:DECLARE @tab1 TABLE (AID INT IDENTITY(1,1), aDescription VARCHAR(30))INSERT INTO @tab1 (aDescription)SELECT 'ABC'UNION ALL SELECT 'DEF'UNION ALL SELECT 'GHI'UNION ALL SELECT 'JKL'DECLARE @tab2 TABLE (BID INT IDENTITY(1,1), AIDReference INT)INSERT INTO @tab2 (AIDReference)SELECT 1UNION ALL SELECT 1UNION ALL SELECT 2UNION ALL SELECT 2UNION ALL SELECT 6UNION ALL SELECT 2SELECT a.AID, b.BID, a.aDescriptionFROM @tab1 AS a INNER JOIN @tab2 AS b ON a.AID = b.AIDReferenceResult:1 1 ABC1 2 ABC2 3 DEF2 4 DEF2 6 DEFFor furhter information, please, see: Visual Representation of SQL Joins[^][EDIT]If you want to enumerate all BIDs for each AID in one row, please, see this:SELECT a.AID, a.aDescription, STUFF( (SELECT',' + CONVERT(VARCHAR(30), b.BID) AS 'text()' FROM @tab2 AS b WHERE a.AID = b.AIDReference FOR XML PATH('')), 1, 1, '') AS [BIDs]FROM @tab1 AS aGROUP BY a.AID, a.aDescriptionResult:AID Desc.. BIDs1 ABC 1,22 DEF 3,4,63 GHI NULL4 JKL NULL[/EDIT]Since TableB contains only BID and AIDRefrence, your inner query is unnecessarily complicated.Replace(Select * from TableB where BID in (select max(BID) from TableB group By AIDRefrence )) Aby(select AIDRefrence as AID, max(BID) as BID from TableB group By AIDRefrence ) ABy the way, there must be some error in the code you show us: Select ... a.description - a does not contain a column "description", it is in TableA.Thanks All But I have Solved this Query myself.Declare @tbl Table(BID int)Insert Into @tblSelect Max(b.BID) from TableA a inner join TableB b on a.aid = b.AIDrefrencegroup By b.AIDRefrenceSelect a.AID, b.BID, a.descriptionFrom tableB B inner Join TableA A on a.aid = b.AIDRefrencewhere b.bid in (Select * from @tbl) 这篇关于内部连接的优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！