我有两个桌子.
表A包含用户ID,用户名
Table A contains UserID, UserName
表B包含ID,FK_UserID,ClientName
Table B contains ID, FK_UserID, ClientName
我需要返回一个不同的A.UserName列表,其中表B中存在A.Username,并且至少附加了一个ClientName,但是在我的查询中,仅查看不同的B.ClientName.
I need to return a list of distinct A.UserName where A.Username exists in table B and has at least one ClientName attached to them, but in my query, only look at distinct B.ClientName.
我的想法是:
Select Distinct A.UserName from A as A Inner Join B as B on A.UserID = B.FK_UserID但这只是表A上的区别
我的下一个想法是:
Select Distinct Username from A where UserID In ( Select FK_UserID, distinct ClientName from B)有人告诉我,有一种方法可以对联接中的两个表进行区分,因为如果表A为300行,而表B为30亿行,那么我的原始查询将花费一些时间.
I was told that there is a way to do a distinct on both tables in the join, because if table A is 300 rows, and table B is 3 BILLION rows, my original query is going to take awhile.
该人特别希望我使用内部联接...
The person specifically wants me to use an inner join...
推荐答案您的原始查询是:
Select Distinct A.UserName from A as A Inner Join B as B on A.UserID = B.FK_UserID;如果b中有许多匹配项,则可能是一个问题.使用in并不是正确的解决方案.而是使用exists:
This can be a problem, if there are many matches in b. Using in isn't quite the right solution. Instead, use exists:
select a.UserName from a where exists (select 1 from b where b.fk_UserID = a.UserId )然后,请确保您在b(fk_UserId)上有一个索引.
Then, be sure that you have an index on b(fk_UserId).
这应该在索引中执行约300次查找.那应该很快.
This should do about 300 lookups in the index. That should be quite fast.
我对告诉您使用内部联接的人的建议:为该人编写一个特殊版本.在运行过程中需要花费几分钟或几小时的时间,让其他人通过exists使用更快的版本.
My advice for the person who told you to use the inner join: Write a special version for that person. For the many minutes or hours that it takes to run, let other people use the faster version using exists.
更多推荐
通过内部联接返回不同值的选项
发布评论