我的表T1
CID Code Addr City State
S10 10 20 bangalore Karnataka
S20 20 35 Hyderabad Telangana
我的结果集应该是这样的
第3列Addr refernce 20是第二行中的colum2代码的值,并且两行的结果集都应该在一行中
CID Code Addr City State City State
S10 10 20 Bangalore Karnataka Hyderabad Telangana
发布于 2021-09-24 18:12:05
declare @table table (
CID varchar(20),
Code varchar(20),
Addr varchar(20),
City varchar(20),
State varchar(20)
)
insert into @table SELECT 'S10','10','20','bangalore','Karnataka'
insert into @table SELECT 'S20','20','35','hyderabad','Telangana'
SELECT
t1.CID,
T1.Code,
T1.Addr,
T1.City,
T1.State,
t2.city,
t2.State
FROM (SELECT Code from @table where addr in (select code from @table) group by code ) codesTable
inner join @table t1 on codesTable.Code = t1.Code
inner join @table t2 on t1.addr = t2.Code
自连接,但指定addr列必须作为代码列存在
发布于 2021-09-24 19:09:35
这是一个直接的自连接,为了你想要的结果,你只需要:
select t1.*, t2.city, t2.state
from t t1
join t t2 on t2.code=t1.addr
https://stackoverflow.com/questions/69319386
复制相似问题