求一个最优化的查询语句

[gebing0908]

有三个表:
Address {AddressId, postalCode, StreetAddress, City, Province, Country}
User {UserId, LastName, FirstName}
User_Address {UserId, AddressId}

要求用PostalCode, StreetAddress, City, Province, Country,LastName 查到重复注册的用户.

如果用户A和用户B,C的LastName,以及地址一样,但是UserId不一样,这样B,C对于A就是两个重复注册的用户.

查询结果要求格式如下:

SourceUserId | DestinationUserId |
A.Id | B.Id |
A.Id | C.Id |
C.Id | A.Id |
C.Id | B.Id |
B.Id | A.Id |
B.Id | C.Id |

[mugua604]

Address {AddressId, postalCode, StreetAddress, City, Province, Country}
User {UserId, LastName, FirstName}
这两表的关联在那?
--try

select b.UserId
from Address a left join User b on a.AddressId=b.UserId
group by b.LastName
having count(1)>2

[qizhi_t]

select n.UserId,m.UserId from
(select a.UserId,b.AddressId,a.LastName from [User] a left join User_Address b on a.UserId=b.UserId) n
left join
(select a.UserId,b.AddressId,a.LastName from [User] a left join User_Address b on a.UserId=b.UserId) m
on m.LastName=n.LastName and m.AddressId=n.AddressId and n.UserId<>m.UserId

[Yang_]

select
u1.UserId as SourceUserId ,u2.UserId as DestinationUserId
from
Address a1,User u1,User_Address ua1,
Address a2,User u2,User_Address ua2
where
a1.AddressId=ua1.AddressId and
ua1.UserId=u1.UserId and
a2.AddressId=ua2.AddressId and
ua2.UserId=u2.UserId and
u1.UserId<>us.UserId and
a1.PostalCode=a2.PostalCode and
a1.StreetAddress=a2.StreetAddress and
a1.City=a2.City and
a1.Province=a2.Province and
a1.Country=a2.Country and
u1.LastName =u2.LastName