内部连接的优化查询 [英] Optimized Query for inner Join
问题描述
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
从（< span class =code-keyword>选择 * 来自 表 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 < span class =code-keyword> INT IDENTITY （ 1 ， 1 ），AIDReference INT ）
INSERT INTO @ tab2 （AIDReference）
SELECT 1
UNION ALL SELECT 1
UNION < span class =code-keyword> 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 < span class =code-sdkkeyword> @ 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 EveryOne
I have Two Table TableA and TableB
Table A contains
AID | Description
1 | ABC
2 | DEF
3 | GHI
4 | JKL
TableB Contains
BID| AIDRefrence
1 | 1
2 | 1
3 | 2
4 | 2
5 | 6
6 | 2
My Output Table must be like This
TableC
AID | BID | Description
1 | 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 Like
Select 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.AIDRefrence
Only 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 1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL 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
Result:
1 1 ABC 1 2 ABC 2 3 DEF 2 4 DEF 2 6 DEF
For 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 a GROUP BY a.AID, a.aDescription
Result:
AID Desc.. BIDs 1 ABC 1,2 2 DEF 3,4,6 3 GHI NULL 4 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 )) A
by
(select AIDRefrence as AID, max(BID) as BID from TableB group By AIDRefrence ) A
By 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 @tbl Select Max(b.BID) from TableA a inner join TableB b on a.aid = b.AIDrefrence group By b.AIDRefrence Select a.AID, b.BID, a.description From tableB B inner Join TableA A on a.aid = b.AIDRefrence where b.bid in (Select * from @tbl)
这篇关于内部连接的优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
