php - 从查询中获取 X 个最受欢迎的标签

Question

$query = "SELECT tag.name, tag.id
        FROM #__k2_tags as tag
        LEFT JOIN #__k2_tags_xref AS xref ON xref.tagID = tag.id
        WHERE xref.itemID IN (".implode(',', $IDs).")
        AND tag.published = 1";

在xref.tagID中是标签ID，例如:tagID:42是100次，tagID:15是90次，tagID:56是10次，我想先显示42，然后15，然后56(我想知道有多少个标签)在 xref.tagID 和顺序中)

Answer 1

试试这个:

$query = "
SELECT *
FROM (
    SELECT tag.name, tag.id, count(xref.id) AS tagCount
    FROM #__k2_tags as tag
    LEFT JOIN #__k2_tags_xref AS xref ON xref.tagID = tag.id
    WHERE xref.itemID IN (".implode(',', $IDs).") AND tag.published = 1
    GROUP BY tag.name, tag.id
)
ORDER BY tagCount";

子查询中的 GroupBy 允许您计算每种标记类型的标记出现次数。
主查询需要按聚合值tagCount排序。