你们可以利用这一问询来获得点数:

SELECT Tags.TagId, COUNT(Articles.TagId) as Occurances
  FROM Articles
  JOIN Tags ON Articles.TagId
  GROUP BY Tags.TagId

它可以用于一种观点或储存程序,而且你可以设立网站,按需要经常加以更新。

为了能够有一个以上标的,你必须增加另一个表格,把本表连接到tag。 它称作许多关系。

article
  article_id
  content

article_tag
  article_id
  tag_id

tag
  tag_id
  tagname

Doing like this, article 1 can be attached to tag 2, and the next row can be 1 and 3 and so on, so one article points to many tags. To count a certain tag, you join the Article_Tag and Tag tables, and and count the rows in Article_Tag where Tag.tagname = mysql , for examle.

您可以形成一种指数观点,即把你需要的所有计数汇总起来,并自动保持:

create view TagCounts 
with schemabinding
as select TagId, count_big(*) as Occurances
from dbo.ArticleTags
group by TagId;
go

create unique clustered index cdxTagCounts on TagCounts (TagId);
go

Now the TagCounts.Occurances field is automatically maintained by SQL Server whenever you insert/delete/update the Articles table. You can query it like:

select Occurances from dbo.TagCounts with (noexpand) where TagId = ...;

And you can cache the result with LinqToCache, as such a query matches the restrictions of Query Notifications.

The trade off of using a pre-aggregated indexed view is scalability: as update of any article updates the count of Occurances for the tags of the article, an exclusive lock is required to update this count. Which implies that only one transaction can use a TagId at any moment. Depending on your traffic and on other elements of your design this restriction may or may not be acceptable.

The other alternative is a table of counts. Front ends (your ASP.Net farm) read this counts and then they update the in-memory count for each operation, keeping track of the delta from the counts in the table. Periodically the front ends merge their deltas into the table (eg. every 5 minutes) and refresh the in-memory table. This way front ends see a stale version of the truth, but an user sees immediate feedback of its actions: because of session stickiness his HTTP requests are processed by the same front end, and thus he see immediately his own article updates triggering modifications to the tag counts. User though do no immediately see the updates from other users that are load-balanced to another front end. Because a crash of the front end (or a process recycle...) will loose the deltas kept so far, the count table will drift in time away from the truth and would have to be periodically updated to the true count in the database.

如果您的准确性更高(所有用户都立即看到真实数字),那么你就能够做一些基于快速的内层关键价值储存的产品,这些储存与我的第一个建议基本相同,但通过量/排位相对性,或许是以