按包含某个字段的记录数的顺序选择记录 [英] Selecting records in order of the number of records that contain a certain field

问题描述

我们有一个这样安排的表:ID、Album_ID 和 Time_Voted.每次用户投票时，它创造了一个新的记录.我们想显示前五个 Album_ID(每周结束时包含该 Album_ID 的记录最多).我知道如何做到这一点的唯一方法是选择过去一周的所有记录，然后在结果上运行while"，然后遍历一个数组，并在每次找到一个时向相应的 Album_ID 键添加一个 +1相同 Album_ID 的实例.这是非常低效的，我相信有一种通过 SQL 查询来做到这一点的好方法，但这超出了我的知识水平.

We have a table that is arranged as such: ID, Album_ID and Time_Voted. Each time a user votes, it creates a new record. We'd like to display the top five Album_IDs, (which had the most records containing that Album_ID at the end of every week). The only way I know how to do this is to SELECT all records from the past week, then run a "while" on the results,then iterate through an array and and add a +1 to the corresponding Album_ID key each time it finds an instance of the same Album_ID. This is quite inefficient, and I'm convinced there's a good way to do this via SQL query, but it's beyond my level of knowlege.

那么问题来了，有没有办法通过查询得到每个专辑ID的数量，然后按照这个数量进行排列?

So the question is, is there a way to, via query, get a count of each number of album_IDs, and then arrange by that count?

这是我使用的原始 SQL 语句.YEARWEEK 的东西是上周才拿到的.

Here's the original SQL statement I was working with. The YEARWEEK stuff was just to get last week.

SELECT * FROM wp_album_votes WHERE YEARWEEK( Time_Voted ) = YEARWEEK( CURRENT_DATE - INTERVAL 7 DAY ) ORDER BY Album_ID

推荐答案

SELECT Album_ID, COUNT(*) AS NumVotes 
    FROM wp_album_votes 
    WHERE YEARWEEK( Time_Voted ) = YEARWEEK( CURRENT_DATE - INTERVAL 7 DAY ) 
    GROUP BY Album_ID
    ORDER BY NumVotes DESC LIMIT 5

这篇关于按包含某个字段的记录数的顺序选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！