在PHP mySql中按频率排序关键字

I've got a database with video ids and N keywords for each video. I made a table with 1 video ID and 1 keyword ID in each row.

我有一个数据库，里面有视频id，每个视频有N个关键字。我做了一个表，每一行有1个视频ID和1个关键字ID。

What's the easiest way to order keywords by frequency? I mean to extract the number of times a keyword is used and order them.

按频率排序关键词最简单的方法是什么?我的意思是提取使用关键字的次数并对它们进行排序。

Is it possible to do that with sql or do I need to use php arrays?

是否可以用sql来实现这一点，还是我需要使用php数组?

Thanks

谢谢

2 个解决方案

#1

I don't see the need for a join here. Simply list all the keywords along with the number of times the keyword appears, ordered from most frequent to less frequent.

我不认为这里需要加入。简单地列出所有关键字以及关键字出现的次数，从最常见到较少频繁。

SELECT keyword, COUNT(*) freq 
FROM keywordTable 
GROUP BY keyword 
ORDER BY freq DESC

#2

If I understand you correctly you can try

如果我没听错，你可以试试

SELECT  VideoID,
        KeyWordID,
        COUNT(KeyWordID) Total
FROM    VideoKeywords
GROUP BY VideoID,
        KeyWordID
ORDER BY VideoID,Total DESC

#1