如何在sql server的一个表中查找最常用的记录 [英] how to find most used records in one table in sql server

问题描述

如何在sql server的一个表中查找最常用的记录

在此先感谢

how to find most used records in one table in sql server

thanks in advance

推荐答案

据我所知，查询的历史记录未存储在任何地方，因此这是不可能的，至少不能通过直接使用SQL来实现.

原则上，您可以创建一些服务，该服务将请求重定向到数据库，并将SQL查询的统计信息或历史记录存储在单独的数据库中.这样，可以保留此类统计信息或查询历史记录，但仅保留通过此服务执行的请求的统计信息或历史记录.例如，如果要考虑的所有查询都是通过Web应用程序或Web服务完成的，则可以完成此操作.

—SA

To best of my understanding, the history of queries are not stored anywhere, so this is impossible, at least not via using SQL directly.

In principle, you can create some service which re-directs requests to the database and stores statistics or history of the SQL queries in a separate database. In this way, such statistics or history of queries could be preserved, but only for the requests performed via this service. For example, it can be done if all the queries to be taken into account are done via a Web application or a Web service.

—SA

正如SA所说，至少通过直接使用SQL，以上"是不可能的.
所有查询历史记录都存储在.LDF文件(日志数据文件)中，但我们无法直接读取.

您需要相应地自定义代码，并在数据库中设置选择计数器.当任何列命中之前，在获取列值之前增加了该计数器.

As SA said "ABOVE" thing is not possible at least via using SQL directly.
all Queries history stored in .LDF file(Log data file) but we can not read it directly.

You need to customize your code accordingly and set select counter in database. when any column hit increase that counter before fetching column value.

AFAIK，您不能.我认为您想显示类似Popular，Most rated，Most viewed等的结果，为此，您需要使用类似算法的方法.因此，您每次都需要在表格中存储其他值(例如评分，计数，观看次数).

看看这些CP文章
CodeProject的文章投票系统 [ CodeProject的投票系统真的很聪明吗? [人气算法 [ ^ ]
电影推荐算法 [什么是计算得分最高的最佳算法项目? [>从数据库中选择最受欢迎的地方的算法 [ ^ ]

AFAIK you can''t. I think you want show the results like Popular, Most rated, Most viewed, etc., For that you need to use something like algorithms. So you need to store additional values(like rating, counts, views) in your table every time.

Look at these CP articles
CodeProject''s Article Voting System[^]
Is CodeProject''s Voting system really smart?[^]

Check these
Popularity Algorithm[^]
Algorithm for movie recomendations[^]
What is the best algorithm to calculate the most scored item?[^]
Algorithm To Select Most Popular Places from Database[^]

这篇关于如何在sql server的一个表中查找最常用的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！