mysql - Query the unique values ​​of a certain field in the database, sorted by time, how to write it?

Question

The article model is as follows

class Article(models.Model):

title = models.CharField(max_length=150, verbose_name='文章标题', unique=True)
content = models.TextField(verbose_name='文章内容')
describe = models.CharField(max_length=500, verbose_name='文章描述', blank=True, null=True)
date_publish = models.DateTimeField(auto_now_add=True, verbose_name="发布时间")
click_count = models.PositiveIntegerField(verbose_name='点击次数', blank=True, null=True, default=0)
keywords = models.ForeignKey(Keywords, blank=True, null=True, verbose_name='关键词')  

class Meta:
    verbose_name = '文章'
    verbose_name_plural = verbose_name
    ordering = ['-date_publish']

def __str__(self):
    return self.title

Query: If there are multiple pieces of data under the foreign key field "keywords", only one piece will be taken. From the multiple pieces of data, the one with the largest number of clicks will be taken according to click_count, and then the value of title/content/description will be obtained and output to the web page template.

I want to use a query syntax, how to write it

Answer 1

Keyword mysql analysis function (window function), since mysql does not support max over, it can only be written as follows:

SELECT tt.*
FROM table tt
  INNER JOIN (SELECT
                keyword,
                max(click_count) maxcount
              FROM table
              GROUP BY keyword) groupedtt ON tt.keyword = groupedtt.keyword AND tt.click_count = groupedtt.maxcount
ORDER BY tt.create_time