更新时间:2023-01-29 09:53:23
我不确定你想要实现什么,但请看下面的例子: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 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)