Join 不行,子查询也很糟糕,那怎么办?
首先,对这个非描述性的标题感到抱歉,我太匆忙了,所以无法想出更好的标题。
第二:
我的数据库的一部分如下图所示:
我的系统上有贡献者,每个贡献者都写入多个源,并且一个源可以有许多正在工作的贡献者。用户可以订阅任意数量的贡献者和任意数量的来源。现在,我想做的只是检索特定用户的所有文章。这些文章要么来自贡献者,要么来自用户订阅的来源。为了简单起见,当用户订阅源时,我只需将所有源贡献者复制到 users_contributors 表中。一个棘手的问题是,当我检索用户的文章时,我会检索他的贡献者撰写的所有文章,以及他所关注的来源中发布的所有文章,而这些文章在系统上没有有效的贡献者。 (IE贡献者ID为空)。
我创建了以下查询:
Select Articles.ArticleID, Articles.ContributorId, Contributors.Name,
Sources.Name, Articles.ArticleTitle
From Articles
Inner Join Contributors On Articles.ContributorId = Contributors.ContributorId
Inner Join Sources On Articles.SourceId = Sources.SourceID
Where Articles.ContributorId in (
Select ContributorId from Users_Contributors
Where UserID = 3
)
OR (
Articles.SourceId in (
Select SourceId from Users_Sources
Where UserID = 3
)
and
Articles.ContributorId is null
)
上述查询的问题是,它不会返回任何contributorID null 的文章。我知道这是因为贡献者表上的连接。这种情况我该怎么办?
- 我应该考虑非规范化吗?
- 针对此查询,要在每个表上建立索引的探测器字段是什么 快速运行(返回的行集是 大约 10000)?
我需要支持此查询的分页,“With { }”子句是否可以 适合我,或者我应该 考虑另一种策略吗?
提前致谢。
Ps:我使用的是 SQL Server 2008
first of all, sorry for the non descriptive title, I'm just too rushed so I couldn't come up with a better one.
Second:
I have a portion of my database the looks like the following diagram:
I have contributors on the system, each write to many sources, and a source can have many working contributors. Users can subscribe to as many contributors as they like and as many sources as they like. Now, what I want to do is simply retrieve all the articles for certain user. These articles are either coming through a contributor or a source the user subscribes to. To make it easy, when a user subscribes to a source I simply copy all the sources contributors to the users_contributors table. One tricky piece, when I retrieve the user's articles I retrieve all the articles that he his contributors write, and all the articles that were published in the sources he follows where those articles doesn't have a valid contributor on the system. (I.E contributorID is null).
I created the following query:
Select Articles.ArticleID, Articles.ContributorId, Contributors.Name,
Sources.Name, Articles.ArticleTitle
From Articles
Inner Join Contributors On Articles.ContributorId = Contributors.ContributorId
Inner Join Sources On Articles.SourceId = Sources.SourceID
Where Articles.ContributorId in (
Select ContributorId from Users_Contributors
Where UserID = 3
)
OR (
Articles.SourceId in (
Select SourceId from Users_Sources
Where UserID = 3
)
and
Articles.ContributorId is null
)
The problem with the above query is that, it doesn't return any article with contributorID null. I understand this is because of the join on the contributors table. What should I do in such a case?
- Should I consider denormalization?
- What are the prober fields to index on each table for this query
to run fast (Rowset returned are
approximately 10000)? I need to support paging on this query, will "With { }" clause be
appropriate to me, or should I
consider another strategy?Thanks in advance.
Ps: I'm using SQL Server 2008
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果您需要分页,请使用此(获取从
80
到100
的页面):If you need paging, use this (to get the pages from
80
to100
):为什么不把这个连接设为
外部连接呢?
这将导致它返回所有文章,无论是否存在匹配的 SourceID(包括 ContributorID 为 null 的情况)。
Why don't you just make this join
an outer join?
That will cause it to return all Articles, whether or not there is a matching SourceID (includes cases where ContributorID is null).