問題描述

文章模型如下

class Article(models.Model):

title = models.CharField(max_length=150, verbose_name=’文章標(biāo)題’, unique=True)content = models.TextField(verbose_name=’文章內(nèi)容’)describe = models.CharField(max_length=500, verbose_name=’文章描述’, blank=True, null=True)date_publish = models.DateTimeField(auto_now_add=True, verbose_name='發(fā)布時(shí)間')click_count = models.PositiveIntegerField(verbose_name=’點(diǎn)擊次數(shù)’, blank=True, null=True, default=0)keywords = models.ForeignKey(Keywords, blank=True, null=True, verbose_name=’關(guān)鍵詞’) class Meta: verbose_name = ’文章’ verbose_name_plural = verbose_name ordering = [’-date_publish’]def __str__(self): return self.title

查詢：若外鍵字段“keywords”下有多條數(shù)據(jù)，只取1條，多條數(shù)據(jù)按click_count取點(diǎn)擊數(shù)最大的，再得到標(biāo)題/內(nèi)容/描述的值，輸出到網(wǎng)頁模板上。

想用一個(gè)查詢語法完成，請(qǐng)問如何寫呢

問題解答

關(guān)鍵字mysql分析函數(shù)(開窗函數(shù)),由于mysql不支持max over只能寫成如下:

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