Mysql:使用 Limit 和 Con

Question

我得到了这个查询，它为我提供了特定国家/地区的所有评级:

SELECT DISTINCT Country, GROUP_CONCAT(rating ORDER BY rating DESC ) AS Ranking
FROM final
group by Country
ORDER BY Country;

表格是这样的:

"Country"   "Rating"    "Title" 
" zimbabwe" "0" "Biology Study Guide" 
" zimbabwe" "0" "Impact: A Guide to Business Communication (Esl English 2nd Language Series)" 
" zimbabwe" "0" "Wayside School Gets a Little Stranger (Wayside School)" 
" ysa"  "5" "Side effects" 
" ysa"  "9" "The Last Precinct" 
" ysa"  "0" "The Horse Whisperer" 
" x"    "0" "The Evi 
" yugoslavia"   "0" "Wild Animus" 
" yugoslavia"   "8" "Complete Works of William Shakespeare (Wordsworth Royals Series)" 
" yugoslavia"   "3" "Wild Animus"

我遇到了两个问题。我试图将每个国家/地区的收视率限制在 10 个以内。我在 DESC 之后添加了 LIMIT 10 并且出现语法错误。

第二个问题是，在降序排列中，10 小于 9，因为它以 1 开头。我该如何处理？

Answer 1

为了限制GROUP_CONCAT()，您可以使用SUBSTRING_INDEX() 来剪切需要的元素。要将字符串作为数字排序，您可以为此使用 CAST()。所以你的查询应该是这样的:

SELECT DISTINCT
    Country,
    SUBSTRING_INDEX(
        GROUP_CONCAT(rating ORDER BY CAST(rating AS UNSIGNED) DESC), ',', 10
    ) AS Ranking
FROM
    final
GROUP BY
    Country
ORDER BY
    Country;